MySQL master-Slave Synchronization (replication)

Directory:

MySQL master-slave synchronization definition

Master-Slave synchronization mechanism

Configuring Master-Slave synchronization

Configuring the primary server

Configure the slave server

Use master-slave synchronization to back up

Use Mysqldump to back up

Back up the original file

Tips for master-slave synchronization

Wrong row

Slave_io_running:no

Slave_sql_running:no

MySQL master-slave synchronization definition

Master-Slave synchronization allows data to be replicated from one database server to another, when data is replicated, one server acts as the primary server (master), and the remaining servers act as slave servers (slave). Since replication is asynchronous, it is not necessary to keep up with the primary server from the server, and it can even connect to the primary server intermittently from the server. Configuration files allow you to specify that you replicate all databases, a database, or even a table on a database.

Benefits of using master-slave synchronization:

1. By increasing the performance of the database from the server, by performing writes and updates on the primary server, and by providing read-out from the server, you can dynamically adjust the number of slave servers to adjust the performance of the entire database.
2. Improve data security-because the data is replicated to the slave server, the replication process can be terminated from the server, so you can back up the data from the server without destroying the primary server
3. Generate real-time data on the primary server and analyze the data from the server to improve the performance of the primary server

Note that MySQL is replicated asynchronously, and MySQL cluster is replicated synchronously. There are many methods of master-slave synchronization, but there are two core methods, the Statement Based Replication (SBR) is based on SQL statement replication, the other is row Based Replication (RBR)-based replication, or you can use mixed Based Replication (MBR). In mysql5.6, SBR is used by default. MySQL 5.6.5 and later versions are based on the global transaction identifiers (Gtids) for transactional replication. When using Gtids, the replication process can be greatly simplified because the gtids is completely transaction-based and as long as the transaction is committed on the primary server, the transaction must be executed from the server.

Specify the format to use by setting the server's system variable Binlog_format:

1.SBR: When using a binary log, the primary server writes SQL statements to the log and then executes the log from the server, which is SBR, which can only be used in versions prior to mysql5.1.4. The use of SBR will have the following

Strengths:

1. Smaller log files
2. All statements are recorded and can be used for future audits

Disadvantages:

1. Statements that use the following functions cannot be copied correctly: Load_file (); UUID (), Uuid_short (); User (); Found_rows (); Sysdate (); Get_lock (); Is_free_lock (); Is_used_lock (); Master_pos_wait (); Rand (); Release_lock (); Sleep (); Version ();
2. The following warning message appears in the log incorrectly: [Warning] Statement is not safe to log in Statement format.
3. Or appear in the client show warnings
4. Insert ... Select statement executes a large number of row-level lock tables
5. The UPDATE statement performs a number of row-level lock tables to scan the entire table

2.RBR: The primary server writes the table's row changes as events to the binary log, and the primary server replicates events representing row changes to the slave service, using RBR's

Strengths:

1. All data changes are replicated, which is the safest way to replicate
2. Fewer row-level lock tables

Disadvantages:

1. The logs will be big.
2. You cannot audit executed SQL statements by viewing the log, but you can use the Mysqlbinlog
3. --base64-output=decode-rows--verbose to see the changes in the data

3.MBR: Use SBR as well as RBR, using SBR by default

Master-Slave synchronization mechanism

The master-slave synchronization between MySQL servers is based on the binary logging mechanism, where the primary server uses binary logs to record the changes to the database, which is maintained by the server by reading and executing the log file to keep the data consistent with the master server.

When a binary log is used, all operations on the primary server are logged and a copy of the log is received from the server. From the server, you can specify which class of events in the log are executed (such as inserting data only or updating data only), and all statements in the log are executed by default.

Each slave server logs information about the binary log: The file name and the processed statements, which means that different slave servers can perform different parts of the same binary log separately, and that the server can connect to or disconnect from the server at any time.

The primary server and each slave server must be configured with a unique ID number (there is a Server-id configuration entry under the [Mysqld] module of the my.cnf file), and each slave server also needs to pass the change MASTER To statement to configure the IP address of the primary server to which it is connected, the log file name and the location within the log (this information is stored in the database of the master server)

Configuring Master-Slave synchronization

There are many ways to configure master-slave synchronization, which can be summarized as follows:

1. On the primary server, you must turn on the binary logging mechanism and configure a separate ID

2. On each slave server, configure a unique ID to create an account that specifically replicates the primary server data

3. Log the location information of the binaries on the primary server before starting the replication process

4. If you already have data in the database before you start copying, you must first create a snapshot of the data (you can export the database using mysqldump, or copy the data file directly)

5. Configure the IP address and login authorization of the primary server to be connected from the server, the binary log file name and location

Configuring the primary server

1. Change the configuration file, first check whether the my.cnf file on your primary server is configured with Log-bin and Server-id under the [mysqld] Module

[Mysqld]log-bin=mysql-binserver-id=1

Note that the above Log-bin and Server-id values can be changed to other values, if there is no above configuration, first shut down the MySQL server, and then add up, and then restart the server

2. Create a user, each slave server need to use an account name and password to connect to the primary server, you can create an account for each from the server, you can also have all the server use the same account. The following creates an account for all slave servers of the same IP segment that can only be used for master-server synchronization.

First log in to MySQL, and then create a user named rep, password 123456 account, the account can be used by all IP addresses under the 192.168.253 network segment, and the account can only be master-slave synchronization

MySQL > Grant replication slave on * * to ' rep ' @ ' 192.168.253.% ' identified by ' 123456 ';

3. Get information about the binary log and export the database, step:

Log in to the database first, then refresh all the tables and add a lock to the database to prevent any write operations to the database.

MySQL > Flush tables with read lock;

Then execute the following statement to get information about the binary log

MySQL > Show master status;

The value of file is the filename of the binary log that is currently used, and position is the location information in the log (no need to tangle what this means), remember that these two values will be used in the following configuration from the server.

Note: If the previous server is not configured to use binary log, then using the above SQL statement will show empty, after the lock table, then export the data in the database (if there is no data in the database, you can ignore this step)

[[Email protected] backup] # mysqldump-uroot-p ' 123456 '-s/data/3306/data/mysql.sock--all-databases >/server/backup/mysql_bak.$ (Date +%F ). SQL

If the amount of data is large, you can compress it to the original approximate one-third when you export it.

[[Email protected] backup] # mysqldump-uroot-p ' 123456 '-s/data/3306/data/mysql.sock--all-databases | gzip >/server/backup/mysql_bak.$ ( Date +%f). sql.gz

The database can then be unlocked to restore operations to the primary database

mysql > Unlock tables;

Configure the slave server

First check that the my.cnf file from the server is already configured under the [mysqld] module Leserver-id

[Mysqld]server-id=2

Note that the value of the Server-id above can be changed to a different value (it is recommended to change to the last field of the IP address), if there is no above configuration, first shut down the MySQL server, and then add up, and then restart the server

If there are multiple slave servers, the Server-id configured on each server must be inconsistent. There is no need to configure Log-bin from the server, but you can also configure the Log-bin option because data backup and disaster recovery can be done from the server, or one day make this from the server into a master server

If the primary server exports data, import the file below and ignore this step if the primary server does not have data

[[Email protected] ~] # mysql-uroot-p ' 123456 '-s/data/3306/data/mysql.sock </server/backup/mysql_bak.2015-07-01.sql

If you have compressed files from the master server, unzip and import them first.

To configure synchronization parameters, log in to MySQL and enter the following information:

mysql> change MASTERto-master_host= ' master_host_name ',master_user=' replication _user_name ',master_password=' Replication_password ',master_log_file= ' Recorded_log_file_ Name ',

：

Start master-Slave synchronization process

mysql > Start slave;

Check Status

MySQL > Show slave status \g

All two of the above processes show yes to indicate a successful configuration

Use master-slave synchronization to back up

Copy the data from the master server to the slave server, then back up the data from the server, use the mysqldump command when the amount of data is not large, and back up the data file directly for a large database.

Use Mysqldump to back up

Step: (All of the following actions are performed from the server)

1. First pause the replication process from the server

Shell > Mysqladmin stop-slave

Or just pause the SQL process (the events from the server that still receive the binary log, but do not execute these events, which can speed up the replication progress as the SQL process is restarted)

Shell > mysql-e ' stop slave sql_thread; '

2. Export all or part of a database using mysqldump

Shell > mysqldump--all-databases > Fulldb.dump

3. After exporting the database, restart the replication process

Shell > Mysqladmin start-slave

Back up the original file

In order to ensure the integrity of the data file, before the backup is first shut down from the server, step:

1. Close the slave server:

Shell > Mysqladmin shutdown

2. Copy the data file, you can use the compression command, if the current directory is the data directory of the database (the value of the configuration item in the MY.CNF file DataDir is the location of the directory)

Shell > Tar cf/tmp/dbbackup.tar./data

3. Then start the MySQL server

Tips for master-slave synchronization

The primary server imports the data for the first time, and if you get the data from somewhere else to import to the master server, you can import it once in the primary server, because it is automatically sent to the slave server, and the command is used on the primary server.

Shell > Mysql-h Master < other_data.sql

Increase from the server, originally already had at least one from the server (temporarily named Slave1), decided to add the rest of the slave server (slave2), at this time do not need to operate as above the main server, as long as the replication of an existing slave server can be

Wrong slave_io_running:no

This is a very common mistake (I have also been gnashing my teeth on this error) and summed up it for three reasons:

1. The primary server's network is not connected, or the primary server's firewall rejected the external connection 3306 port
2. When you configure the slave server, the wrong IP address and password are lost, or the primary server incorrectly writes the user name and password when creating the user
3. The binary log information of the primary server is incorrectly configured when you configure the slave server

Troubleshooting process: (primary server ip:192.168.1.139, from server ip:192.168.1.204)

The No. 0 step is to check the error log, if you cannot quickly debug, you can follow my steps to try:

1. First, Ping the program from the server to make sure you can ping the primary server

To perform a mysq remote connection from the server

[[email protected] log] # mysql-urep-p-H 192.168.1.139-p3306

If error 1045 (28000) is displayed: Access denied for user ' test ' @ ' 192.168.1.204 ' (using Password:yes) jumps to 3rd

2. Log on to the master server for MySQL, view all users

MySQL > select user,host from Mysql.user;

Is my source of error, you can see the user name is completely wrong, first delete the wrong user:

mysql > Drop user "[email protected]%" @ "%";

and re-create the user:

MySQL > Grant replication slave on * * to ' rep ' @ ' 192.168.1.% ' identified by '123456 > Flush privileges;

3. If the user name is not wrong, then how to rule out whether it is an input password error?

Well, I'd like to know the way. The best thing to do is to type a few more times, or recreate the user name and password to test. The problem is not resolved, go to 4

4. Add Port 3306 to your firewall

[[email protected] MySQL] # firewall-cmd--zone=public--add-port=3306/tcp--permanent [root@localhost MySQL] # Firewall-cmd--reload

Then close SELinux

[[email protected] log] # Vi/etc/sysconfig/selinux

Change Selinux=enforcing to Selinux=disabled

[[email protected] log] # Source/etc/sysconfig/selinux

Log on to the master server to view server status

MySQL > Show master Status \g

Then reconfigure the slave server once, before you configure the master/subordinate synchronization process to first shut down

mysql > Stop slave;

Outside of the method, I have not tried

Slave_sql_running:no

After debugging the above slave_io_running to Yes, it's my turn to the demo. I am based on the content of this blog to solve: http://kerry.blog.51cto.com/172631/277414

MySQL master-Slave Synchronization (replication)