MySQL5.6 master-slave replication (mysql Data Synchronization configuration), mysql5.6mysql

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)