MySQL backup and AB Replication

Mysql backup and AB replication are available in two ways: Synchronous replication (msylq-cluster) asynchronous replication (mysql-ms master-slave backup is also called dual-machine Hot Backup) I. Mysql full backup (

Mysql backup and AB replication are available in two ways: Synchronous replication (msylq-cluster) asynchronous replication (mysql-m/s master-slave backup is also called dual-machine Hot Backup) I. Mysql full backup (

Mysql backup and AB Replication
There are two replication methods: Synchronous replication (msylq-cluster) asynchronous replication (mysql-m/s master-slave backup is also called dual-machine Hot Backup)

I. Mysql full backup (pure copy mode and mysqldump tool)
1. Pure replication: (the mysql service must be stopped)
/Etc/init. d/mysqld stop
Tar-zcvf mysql_backup.tgz/var/lib/mysql/
/Etc/init. d/mysqld start
Recovery Method: Decompress the tar package to overwrite the original path file. The disadvantage is that data of different versions is incompatible.
2. mysqldump tool:
Mysqldump-uroot-p test>/srv/test. SQL (Backup test database)
Mysqldump-uroot-p -- all-databases>/srv/alldata. SQL (back up all databases)
Recovery Method: mysql-uroot-p test </srv/test. SQL (import the test database)
Mysql-uroot-p </srv/alldata. SQL (import all data)

Ii. Incremental Backup (recovery by time and recovery by location number)
Mysql Incremental backup is implemented using mysql binary logs. The binary logs store all SQL statements for mysql operations; mysql's binary log function is disabled by default. You need to manually enable it;
Open Mode: vim/etc/my. cnf add
Log-bin = noah: log-bin = binlog by default;
After the service is restarted, The binlog binary file is found in/var/lib/mysql;
To view binary log files, run mysqlbinlog;

1. Use time to restore:
For example: mysqlbinlog -- start-date = "9:46:04" -- stop-date = "13:47:24"/var/lib/mysql/binlog.000004 | mysql-uroot-pwestos (account root password westos, execute SQL statements for the start and stop periods to mysql to restore the data during this period)
2. Use the location number to restore:
For example, mysqlbinlog -- start-position = "106" -- stop-position = "1472"/var/lib/mysql/binlog.000003 | mysql-uroot-pwestos
Note: You can use the mysqlbinlog command to view the time and position number.

Iii. Mysql AB replication: (synchronous and asynchronous replication)
The following is a one-way hot standby experiment:
Master: 192.168.0.57 slave database slave: 192.168.0.95

1. Install mysql on master and slave
Yum install mysql-server-y (mysql must be installed on both the master and slave hosts)

2. Modify the/etc/my. cnf file on the master and add
Server-id = 1 // difference between master and slave
Log-bin = mysql-bin // start the binary log file Function
Binlog-do-db = test // name of the database whose binary files need to be synchronized (if this parameter is left blank, all data is synchronized)
Binlog-ignore-db = mysql // do not synchronize mysql database data, so as not to log on from the machine because user information is synchronized.
Then restart the service: service mysqld restart

3. Authorize the user:
Master login database:
[Root @ shorttop57 ~] # Mysql-uroot-pwestos
Mysql> grant replication slave, reload, super on *. * to 'slave '@ '192. 168.0.95' identified by "192 ";
Mysql> flush privileges;
Authorize the host 192.168.0.95 user slave with a password of 10086.

Test on slave database slave: [root @ login top95 ~] # Mysql-uslave-p10086-h 192.168.0.57
OK, and the user is successfully created.

4. Modify the/etc/my. cnf file on slave and add
Server-id = 2 // slave Database id, which is different from the master database (note that this line of comment is removed; otherwise, mysql cannot start and can view logs)
Master-host = 192.168.0.57 // specify the IP address of the master database
Master-user = slave // specify the authorized user name
Master-password = 10086 // specify the password of the authorized user
Replicate-do-db = test // database to be synchronized
Replicate-ignore-db = mysql // ignore mysql Database Synchronization

5. view the status of the master database on the master database:
[Root @ shorttop57 ~] # Mysql-uroot-pwestos
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 571 | test | mysql |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

Can know File (mysql-bin.000001), Position (571)

6. view the slave database status on slave:
[Root @ override top95 ~] # Mysql-uroot-pwestos

Mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql> change master to master_host = '2017. 168.0.57 ', master_user = 'slave', master_password = '000000', master_log_file = 'mysql-bin.000001 ', master_log_pos = 10086; (specify the task number to be backed up from the slave database, log On with the Server Load balancer password 10086)
Query OK, 0 rows affected (0.13 sec)

Mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

Mysql> show slave status \ G
......
Slave_IO_Running: Yes (read and write from library I/O is normal)
Slave_ SQL _Running: Yes (slave database mysql listener is normal)
......
OK. If not, check whether the configuration file is correct ~~
Let's test whether the slave database slave has synchronized the master data?
[Root @ shorttop57 ~] # Mysql-uroot-pwestos
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Test |
+ -------------------- +
3 rows in set (0.00 sec)

Mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Grade |
+ ---------------- +
1 row in set (0.00 sec)

Mysql> select * from grade;
+ ------- + ----- + ------- + -------- +
| Name | num | grade | notes |
+ ------- + ----- + ------- + -------- +
| Kevin | 14 | 98 | great! |
+ ------- + ----- + ------- + -------- +
1 rows in set (0.01 sec)

OK. It has been synchronized !!