Main Library ip:192.168.1.10
From library ip:192.168.1.11
1, the main library configuration Editor my.cnf:
= mysql-binserver-ID111log-bin-index=mysql-bin.indexsync_binlog= 1 Binlog_format=mixedbinlog-do-db = testdb//Synchronize database Binlog-ignore-db = Mysqlbinlog-ignore-db = performance_schemabinlog-ignore-db = Information_schemabinlog_ Checksum=none
2. Create a Sync Account
mysql> grant replication Slave on * * to [email protected]192.168. 1.11 ' 123456 '
3. Main Library status
mysql> flush Privileges;mysql> show Master Status; +------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| Mysql-bin. 000008 | 337 | | | +------------------+----------+--------------+------------------+
Record the binary log file name and location
4. configuration file from library
# [must] enable binary log-bin=mysql-bin # [must] server unique ID, default is 1, usually take IP last segment server-id=222 relay-log-index = slave-relay-bin.indexrelay-log = slave-relay-1 1 1
5. Configure the connection main Library
mysql> Change Master to master_host='192.168.1.10', master_user='slave ', master_password='123456', master_log_file=' mysql-bin.000008', master_log_pos=337;
6. Start syncing
mysql> start slave;mysql> show slave status\g;
7. Normal state
Slave_IO_Running:YesSlave_SQL_Running:Yes
8, solve the master never sync
First on the Master library:
Mysql>show processlist; See if the next process is too much sleep. Found to be normal.
Show master status; also normal.
Mysql> Show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | Mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in Set (0.00 sec)
Go to the slave and check it out.
Mysql> Show Slave Status\g
Slave_io_running:yes
Slave_sql_running:no
Visible is slave different steps
Two workarounds are described below:
Method one: After ignoring the error, continue syncing
This method is applicable to the case that the data of master-slave database is not very different, or the data can not be completely unified, the data requirements are not strict.
Solve:
Stop slave;
#表示跳过一步错误, the following numbers are variable
Set global sql_slave_skip_counter = 1;
Start slave;
Then use mysql> Show slave status\g to view:
Slave_io_running:yes
Slave_sql_running:yes
OK, now the master-slave synchronization status is normal
Mode two: Re-master from, fully synchronized
This method is suitable for the case that the master-slave database data is large or the data is completely unified.
The steps to resolve are as follows:
1. Advanced Master Library, lock table, prevent data write
Use the command:
Mysql> flush tables with read lock;
Note: This is locked to a read-only state, and the statement is case insensitive
2. Perform data backup
#把数据备份到mysql. bak.sql file
[[email protected] MySQL] #mysqldump-uroot-p-hlocalhost > Mysql.bak.sql
Note here: Database backups must be done regularly to ensure that data is foolproof
3. View Master Status
Mysql> Show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | Mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in Set (0.00 sec)
4. Upload the MySQL backup file to the slave library for data recovery
#使用scp命令
[Email protected] mysql]# SCP mysql.bak.sql [email protected]:/tmp/
5. Stop the state from the library
mysql> stop Slave;
6. Then run the MySQL command from the library, import the data backup
Mysql> Source/tmp/mysql.bak.sql
7. Set synchronization from the library, note that the synchronization point is in the main library show Master status information | file| Position two items
Change Master to Master_host = ' 192.168.128.10 ', master_user = ' slave ', master_port=3306, master_password= ' 123456 ', maste R_log_file = ' mysqld-bin.000001 ', master_log_pos=3260;
8. Re-open from sync
mysql> start slave;
9. View sync Status
Mysql> Show slave status\g View:
Slave_io_running:yes
Slave_sql_running:yes
Mysql 5.6 Master-Slave synchronization configuration and solution