MySQL5.6 master-slave replication (mysql Data Synchronization configuration), mysql5.6mysql
Planning
Master 192.168.56.2
Slave 192.168.56.5
1. On the master database, set a REPLICATION account rep1 and grant the replication slave permission.
mysql> grant replication slave on *.* to 'rep1'@'192.168.56.2' identified by '123456';Query OK, 0 rows affected (0.01 sec)
2. modify the configuration file my. cnf of the master database server, enable BINLOG, and set the value of server-id. The modification of these two parameters takes effect only after the Database Service is restarted.
Vi/etc/my. cnf
[Mysqld]
Log-bin =/home/mysql/log/mysql-bin.log
Server-id = 1
[root@rhel6 ~]# service mysql restartShutting down MySQL.. [ OK ]Starting MySQL. [ OK ]
3. The read lock setting is effective on the master database. This operation is used to ensure that there are no database operations in order to obtain a consistent snapshot.
mysql> flush tables with read lock;Query OK, 0 rows affected (0.02 sec)
4. Obtain the current binary log name and offset value on the master database. This operation aims to recover data from this point after the database is started.
mysql> show master status;+-----------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-----------------+----------+--------------+------------------+-------------------+| mysqlbin.000021 | 120 | | | |+-----------------+----------+--------------+------------------+-------------------+1 row in set (0.03 sec)
5. The master database server has stopped the update operation, and the backup of the master database needs to be generated. There are many backup methods, you can copy all data files directly to the slave database server in the operating system, export data through mysqldump, or use the ibbackup tool to back up the database. If the service of the primary database can be stopped, directly copying data files should be the fastest way to generate snapshots:
[Root @ rhel6 lib] # tar-zcvf mysql.tar.gz mysqlmysql/tar: mysql/mysql. sock: socket ignoredmysql/mysqlbin.000019mysql/test1/mysql/test1/db. optmysql/test1/pack. MYDmysql/test1/myisam2.frmmysql/test1/emp. ibdmysql/test1/mer_myisam.MRGmysql/test1/emp. frmmysql/test1/pri_t.TRG .... transfer from database [root @ rhel6 lib] # scp mysql.tar.gz root@192.168.56.5:/rootroot@192.168.56.5's password: mysql.tar.gz 100% 1402KB 1.4 MB/s
6. The write operation can be restored after the primary database is backed up. The rest only needs to be performed on the slave database.
mysql> unlock tables;Query OK, 0 rows affected (0.02 sec)
7. Restore the consistent backup of the primary database to the slave database. If you use a file package of .tar.gz, you only need to unbind it to the corresponding directory.
tar -zxvf mysql.tar.gz
8. modify the configuration file my. cnf of the slave database and add the server-id parameter. Note that the server-id value must be unique and cannot be the same as the configuration of the primary database. If there are multiple slave database servers, each slave database server must have its own unique server-id value.
Vi my. cnf
[Mysqld]
Server-id = 2
9. On the slave database, use the -- skip-slave-start option to start the database, so that the replication process from the Database Service is not started immediately, it is convenient for us to further configure the service from the database (optional)
Mysqld_safe -- skip-slave-start &
Or modify my. cnf and add the skip-slave-start parameter, service mysql start
10. Set slave database servers to specify the users used for replication, the IP address and port of the master database server, and the log files and locations where replication is started.
mysql> change master to -> master_host='192.168.56.2', -> master_user='rep1', -> master_port=3306, -> master_password='123456', -> master_log_file='mysqlbin.000021', -> master_log_pos=120;Query OK, 0 rows affected, 2 warnings (0.08 sec)
11. Start the slave thread on the slave Database
mysql> start slave;Query OK, 0 rows affected (0.17 sec)
12. view the process from the database
mysql> show processlist \G;*************************** 1. row *************************** Id: 1 User: root Host: localhost db: zxCommand: Query Time: 0 State: init Info: show processlist*************************** 2. row *************************** Id: 4 User: system user Host: db: NULLCommand: Connect Time: 1484 State: Waiting for master to send event Info: NULL*************************** 3. row *************************** Id: 5 User: system user Host: db: NULLCommand: Connect Time: 739 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL3 rows in set (0.00 sec)mysql> show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.2 Master_User: rep1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysqlbin.000021 Read_Master_Log_Pos: 652 Relay_Log_File: rhel6-relay-bin.000002 Relay_Log_Pos: 814 Relay_Master_Log_File: mysqlbin.000021 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 652 Relay_Log_Space: 987 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 3743271b-aa6d-11e6-aa2e-080027e5f5dd Master_Info_File: /mysqldata/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)
When Slave_IO_Running and Slave_ SQL _Running are all set to YES, the build is successful. If an error occurs, view the cause of the error. Two errors I encountered during setup:
(1) Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Cause: During the setup, the entire directory of the master database is copied. The UUID of the master database is the same as that of the slave database. Modify the UUID of the slave database.
Method: Modify $ datadir/auto. cnf in hexadecimal format and restart mysql.
(2) Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Cause: master_log_file is incorrectly configured when you configure change master.
Method: change master to master_log_file = 'mysqlbin. 100'; start slave.
13. Test the correctness of Replication
Master database mysql> create Database zx; Query OK, 1 row affected (0.05 sec) mysql> use zx; database changedmysql> create table t (id int); Query OK, 0 rows affected (0.09 sec) mysql> insert into t values (1), (2), (3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 slave database mysql> show databases; + -------------------- + | Database | + -------------------- + | information_schema | data | mysql | performance_schema | test | test1 | zx | + ------------------ + 7 rows in set (0.00 sec) mysql> use zxReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql> select * from t; + ------ + | id | + ------ + | 1 | 2 | 3 | + ------ + 3 rows in set (0.00 sec)