The problem

Mysql primary and secondary databases are not synchronized today

Master library:

mysql>show processlist;
Copy the code

Check to see if the process is sleeping too much. Found it normal.

show master status;
Copy the code

Also normal.

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin000001. |     3260 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
Copy the code

Check it out on Slave

mysql> show slave status\G                                                
 
Slave_IO_Running: Yes
Slave_SQL_Running: No
Copy the code

The Slave is not synchronized

The solution

There are two solutions

Method 1: Ignore errors and continue synchronization

This method is suitable for the data difference between master and slave libraries is not big, or the data can not be completely unified, data requirements are not strict

Solution:

stop slave; # indicates a step skipped error, and the following number is variableset global sql_slave_skip_counter =1;
start slave;
Copy the code

Mysql > show slave status\G

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Copy the code

Ok, now the master/slave synchronization is normal…

Method 2: Master slave again, complete synchronization

This method is suitable for the situation where the data of master and slave databases differ greatly or the data is required to be completely uniform

The solution steps are as follows:

1. Master the database to lock the table to prevent data writing

Use the following command:

mysql> flush tables with read lock;
Copy the code

Note: The lock is read-only and the statement is case insensitive

2. Back up data

Back up data to mysql.bak.sql

mysqldump -uroot -p -hlocalhost > mysql.bak.sql
Copy the code

Note here: database backup must be done regularly, you can use shell scripts or Python scripts are convenient, to ensure that the data is safe.

3. Check the master status

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin000001. |     3260 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
Copy the code

4. Upload the mysql backup file to the secondary database machine for data restoration

SCP mysql. Bak. SQL [email protected]: / TMP /Copy the code

5. Stop the slave database

mysql> stop slave;
Copy the code

6. Run the mysql command in the secondary database to import the backup data

mysql> source /tmp/mysql.bak.sql
Copy the code

Set from the repository synchronization, pay attention to the synchronization points here, is the main library show master status information in | File | Position two

change master to master_host = '192.168.128.100', master_user = 'rsync',  master_port=3306, master_password=' ', master_log_file =  'mysqld-bin.000001', master_log_pos=3260;
Copy the code

8. Restart secondary synchronization

mysql> start slave;
Copy the code

9. Check the synchronization status

mysql> show slave status\G  

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Copy the code

10. Return to the primary database and run the following command to unlock the table:

UNLOCK TABLES;
Copy the code

Big welfare

WeChat search the ice technology WeChat 】 the public, focus on the depth of programmers, daily reading of hard dry nuclear technology, the public, reply within [PDF] have I prepared a line companies interview data and my original super hardcore PDF technology document, and I prepared for you more than your resume template (update), I hope everyone can find the right job, Learning is a way of unhappy, sometimes laugh, come on. If you’ve worked your way into the company of your choice, don’t slack off. Career growth is like learning new technology. If lucky, we meet again in the river’s lake!

In addition, I open source each PDF, I will continue to update and maintain, thank you for your long-term support to glacier!!

Write in the last

If you think glacier wrote good, please search and pay attention to “glacier Technology” wechat public number, learn with glacier high concurrency, distributed, micro services, big data, Internet and cloud native technology, “glacier technology” wechat public number updated a large number of technical topics, each technical article is full of dry goods! Many readers have read the articles on the wechat public account of “Glacier Technology” and succeeded in job-hopping to big factories. There are also many readers to achieve a technological leap, become the company’s technical backbone! If you also want to like them to improve their ability to achieve a leap in technical ability, into the big factory, promotion and salary, then pay attention to the “Glacier Technology” wechat public account, update the super core technology every day dry goods, so that you no longer confused about how to improve technical ability!