The business scenario

In fact, I wanted to do it for a long time, because I had no opportunity before a small company. Recently, because the historical code was written badly, the mysql load was too high, so the leader allowed to do the database master and slave, hahaha.

Because I have seen a video of mysql master slave, so this time it is relatively smooth, of course, there are still some problems. Ok, beep beep, let’s get down to business

International practice

1. What is mysql master/slave

So what is mysql master slave? In simple terms, there are two mysql servers. When we add or modify data to one database, the other database will be updated synchronously. One is the husband, the other is the wife. Husband hair salary, meeting transfer salary to wife immediately 🙂

MySQL master-slave replication is based on the primary server recording binary logs of database changes, and the secondary server automatically performs updates through the binary logs of the primary server.

General schematic diagram:

That is, enable the binary log of the primary database, and then read the binary log of the primary database from the secondary database to recover the EXECUTED SQL, and then execute it again on the secondary database.

Specific operation Steps

  1. Step 0 of course is to install two mysql servers separately.
  2. Edit the configuration file /etc/my.cnf on the main mysql database

Config under [mydqld] :

Log-bin =mysql-bin/mysql-bin/mysql-bin/mysql-bin/mysql-bin/mysql-bin/mysql-bin/mysql-bin/mysql-bin/mysql-bin # Mixed binlog_format=mixedCopy the code
  1. Edit from the configuration file /etc/my.cnf above mysql

Also under [mysqld] :

Mysql > select * from user where ID = 0 serever-id=2Copy the code
  1. Create primary/secondary synchronization account on primary mysql

Enter the mysql command line interface and run the following SQL:

Mysql > CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; Mysql > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; mysql> GRANT REPLICATION SLAVE ON *.Copy the code
  1. Lock primary mysql to prevent write operations
mysql> FLUSH TABLES WITH READ LOCK;
Copy the code
  1. View the bin-log file and location on the main mysql
mysql> show master status;
Copy the code

After executing the above command, you should see something similar to the following:The file name in the picturemysql-bin.000002And the Position1285It’s very important. We’ll use it next.

The file name and position you see when you execute the command may be different from mine, which is determined according to the data in your database. You can use it according to what is displayed on your computer instead of copying mine completely

  1. Dump the database to be synchronized on the primary mysql database

The mysqldump command needs to be executed in the command line of the operating system, so you need to exit the mysql command line interface and run the following command:

mysqldump --all-databases --master-data > database.bak.sql -uroot -p
Copy the code
  1. Dump data before importing from mysql

Note that we are operating from mysql

This command is executed on the operating system command line. Note the Angle brackets in the command when executing the command,< this is executed on the operating system command line, > this is executed on the mysql command line

mysql < database.bak.sql -uroot -p
Copy the code

8. Configure the primary/secondary connection information on the secondary mysql server

Mysql > CHANGE MASTER TO MASTER_HOST='10.1.1.191', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1285;Copy the code
  • MASTER_HOST: indicates the IP address of the primary mysql database
  • MASTER_PORT: indicates the primary mysql port
  • MASTER_USER: synchronization account name configured for the primary database
  • MASTER_PASSWORD: synchronization account password configured for the primary database
  • MASTER_LOG_FILE: the binary log name I reminded of above
  • MASTER_LOG_POS: binary log position

This command is very important. It depends on whether the configuration succeeds or not. It is here that I made a configuration error at the beginning and failed to configure the first time. 😳😳😳

  1. Start sync from mysql
mysql> start slave;
Copy the code

Mysql > configure primary/secondary status of mysql

mysql> show slave status \G;
Copy the code

If both of these items are Yes, it means success!!

  1. Finally, don’t forget one thing

We just locked the primary mysql database, and now the primary mysql database cannot insert data, so now we need to lock the primary mysql database. Enter primary mysql and execute:

mysql> unlock tables;
Copy the code

Mysql master-slave configuration is now complete

I hope this article will help you and I wish you success