Summary of Mysql Master-Slave backup and recovery
Mysql master-slave implementation
1. Configure the service profile of the master-slave node
1.1.Configuring master nodes:
[mysqld]
Binlog_format=mixed
Log-bin=master-bin
Server-id=1
Port=3306
Datadir=/mydata/data
Socket=/tmp/mysql.sock
1.2.Configuring slave nodes:
[mysqld]
Binlog-format=mixed
Log-bin=mysql-bin.log
Server-id=2
Relay-log=/mydata/relaylogs/relay-bin (need to create / mydata/relaylogs/ after chown-R mysql.mysql / mydata/relaylogs/)
Port=3306
Datadir=/mydata/data
Socket=/tmp/mysql.sock
2. Create a replication user
Lord:
Mysql > GRANT REPLICATION SLAVE ON *. * TO slave@192.168.4.% IDENTIFIED BY '12345'
Mysql > flush privileges
3. Provide the initial dataset for the standby node
Lock the master table, back up the data on the master node, and restore it to the slave node; you need to use the show master status command on the master to check the binary log file name and event location for later use when starting the slave node.
Note: the initial position of the mysql-bin can be seen through the full set.
Show master status can see the current spatial location.
It can be restored through full and incremental backups.
4. In order to test the results, first create a test library on the master machine
Mysql > CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci
Query OK, 1 row affected (0.00 sec)
Mysql > use test
Database changed
Mysql > create table if not exists test (id int (10) PRIMARY KEY AUTO_INCREMENT,name varchar (50) NOT NULL)
Query OK, 0 rows affected (0.02 sec)
Mysql > insert into test.test values (1, "liudehua"), (2, "zhangxueyou")
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
Mysql > select * from test.test
+-+ +
| | id | name |
+-+ +
| | 1 | liudehua |
| | 2 | zhangxueyou |
+-+ +
2 rows in set (0.00 sec)
5. Export more data from the master database than the slave database, and then import it into the slave database. Ensure that the data of both parties are consistent before the implementation of the synchronization environment.
Lock the database before exporting it
Mysql > flush tables with read lock
# mysqldump-uroot-p12345 test > / opt/test.sql
Then upload the exported test.sql file to the slave machine
Scp test.sql root@192.168.4.204:/root/
6. Start the replication thread from the node
Record the mysq-bin file and location, which can be synchronized from the following point
7. Import the data from master into the slave database.
Mysql > CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci; # first create an empty test library, otherwise the following import data will mistakenly say that the library does not exist. The character set needs to be the same as the main library
Mysql > use test
# mysql-uroot-p test
< test.sql 8、准备同步 mysql>Stop slave
Mysql > CHANGE MASTER TO MASTER_HOST='192.168.4.203'
-> MASTER_USER='slave'
-> MASTER_PASSWORD='12345'
-> MASTER_LOG_FILE='master-bin.000004'
-> MASTER_LOG_POS=2327
Mysql > start slave
Mysql > show slave status\ G
9. Finally, unlock the main library and write data for synchronous verification:
Master:
Mysql > unlock tables
Mysql > insert into test.customers values (5, "liming", 55), (6, "guofucheng", 55)
Query OK, 1 row affected (0.00 sec)
Slave:
