MySQL master-slave Replication

As the amount of data increases, a single Mysql database may not meet the actual needs, regardless of security, high availability, and high concurrency.

Therefore, data is usually synchronized through Master-Slave replication (Master-Slave), and then through read/write splitting (MySQL-Proxy) to improve the database's concurrent load capacity for deployment and implementation.

Benefits of using mysql master-slave Replication:

1. The architecture of master-slave servers improves stability. If the master server fails, we can use the slave server to provide services.

2. Separate processing of user requests on the Master/Slave server can improve data processing efficiency.

3. copy the data on the master server to the slave server to protect the data from unexpected losses.

As shown in:

 

The following are my notes for your reference.

I. installation and configuration of MySQL

For specific installation process, it is recommended to refer to my article: http://www.yzswyl.cn/blread-1639.html

It is worth mentioning that my installation process is the compilation and installation of the source code package, and all the configurations and data are uniformly planned into the/opt/mysql directory, therefore, after the installation is complete on a server, you can package the entire mysql directory and then upload it to other servers for uninstallation.

Ii. MySQL master-slave replication (all copies)

Scenario Description:

Master database server: 192.168.10.130, MySQL has been installed, and no application data.

Slave database server: 192.168.10.131, MySQL has been installed, and no application data.

2.1 operations performed on the master server

$ Vi/opt/mysql/etc/my. cnf [mysqld] log-bin = mysql-bin // [required] Enable binary log server-id = 130 // [required] unique server ID. The default value is 1, generally, the last IP segment is used.

# Start the mysql service/opt/mysql/init. d/mysql start # log on to and manage the MySQL server through the command line/opt/mysql/bin/mysql-uroot-p 'new-password' # authorize the SLAVE Database Server 192.168.10.20.mysql> GRANT REPLICATION SLAVE ON *. * to 'rep1' @ '192. 168.10.131 'identified by 'Password'; # query the status of the primary database Mysql> show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000005 | 261 | + usage + -------- + -------------- + ------------------ +

Record the value of FILE and Position, which will be used for subsequent slave server operations.

2.2 configure slave server

$ Vi/opt/mysql/etc/my. cnf [mysqld] log-bin = mysql-bin // [required] Enable binary log server-id = 131 // [required] unique server ID. The default value is 1, generally, the last IP segment is used.

# Start the mysql service/opt/mysql/init. d/mysql start # log on to and manage the MySQL server through the command line/opt/mysql/bin/mysql-uroot-p 'new-password' # execute the synchronous SQL statement mysql> change master tomaster_host = '2017. 168.10.130 ', master_user = 'rep1', master_password = 'Password', master_log_file = 'mysql-bin.000005', master_log_pos = 261; # start the Slave synchronization process mysql> start slave after correct execution; # Check mysql> show slave status \ G ============================== =======================******************** 1. row * Slave_IO_State: Master_Host: 192.168.10.130Master _ User: rep1Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: Unknown relay_log_file: localhost-relay-bin.000008Relay_Log_Pos: 561Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: YESSlave_ SQL _Running: YESReplicate_Do_DB :............... Omitted ............... Master_Server_Id: 11 row in set (0.01 sec) ========================================================== ======

The values of Slave_IO_Running and Slave_ SQL _Running must be YES to indicate that the status is normal.

Note:If the master server already has application data, perform the following operations during master-slave replication:

(1) The master database locks the table and prevents data from being written again.

Mysql> flush tables with read lock;

(2) view the status of the primary database

Mysql> show master status;

(3) record the value of FILE and Position.

Copy the data file of the master server (the entire/opt/mysql/data Directory) to the slave server. We recommend that you archive and compress the file with tar before uploading it to the slave server for decompression.

(4) cancel the master database lock

Mysql> unlock tables;

2.3 verify master-slave Replication

Operations on the master server

# Create database first_dbmysql> create database first_db; Query OK, 1 row affected (0.01 sec) # create table first_tbmysql> create table first_tb (id int (3), name char (10); Query OK, 1 row affected (0.00 sec) on the master server) # insert the record mysql> insert into first_tb values (001, 'myself '); Query OK, 1 row affected (0.00 sec) in the table first_tb on the master server)

View on the slave server

Mysql> show databases; =========================+ -------------------- + | Database | + ------------------ + | information_schema | first_db | mysql | performance_schema | test | + ------------------ + 5 rows in set (0.01 sec) ============================## the database first_db has automatically generated mysql> use first_dbDatabase chagedmysql> show tables; =========================+ -------------------- + | Tables_in_first_db | + ------------------------ + | first_tb | + -------------------- + 1 row in set (0.02 sec) ==============================## the database table first_tb has also been automatically created for mysql> select * from first_tb; =================================+ ------ + | id | name | + ------ + | 1 | myself | + ------ + 1 rows in set (0.00 sec) ========================================## the record already exists

As a result, the entire MySQL master-slave replication process is complete.

Iii. MySQL master-slave replication copies some databases or tables

The difference here is that the Master/Slave server modifies the/etc/my. cnf configuration, which only introduces different situations.

3.1 configure the master server

# In/etc/my. cnf configuration file: log-bin = mysql-bin # Start the binary log system server-id = 130 # unique IDlog-bin in the local database =/var/log/mysql/updatelog # Set the log file name to be generated, the path here does not have the permission to manually create and give it to the mysql user. Binlog-do-db = test1 # binary database name to be synchronized binlog-ignore-db = mysql, test # Avoid synchronizing mysql user configuration to avoid unnecessary trouble

3.2 slave server configuration

# Modify/etc/my. cnf file: server-id = 131 # slave server id, do not use the same master-host = 192.168.20.155 # specify the master server IP address master-user = replication # specify the username master-password = 123456 # password master- port = 3306 # port used for synchronization master-connect-retry = 60 # resumable copy-ignore-db = mysql # block synchronous replicate-do-db = test1 for mysql Databases # synchronized database name replicate_do_table = testa # Name of the synchronized data table

The replicate part can also be written as follows:

Replicate-do-table = database name. table name # replicate-do-table = database name if you want to copy multiple tables as long as they are directly added below (just a few copies are added. table Name

Notes for mysql replication single-table or multi-table replication:

1. The name of the master database and slave database must be the same;

2. The replication between the master database and slave database can be accurate to the table, but slave needs to be restarted immediately when the data structure of the master database or slave database needs to be changed;

3. You cannot directly write the configuration information of the master in the mysql configuration file. You need to use the change master command;

4. Specifying replicate_do_db must be configured in my. ini and cannot be completed using the change master command;

5. if it is not cleared in time, the accumulated binary log file may fill up the disk space. You can add expire_logs_days = 7 to the configuration file to keep the logs for the last seven days, we recommend that you use reset slave to cancel relaylog when slave is no longer used;

The above rules are found during use. I don't know if they are correct or incorrect. I hope you can give me some advice.

Finally, write a shell script and use nagios to monitor two "yes" of slave. If only one or zero "yes" is found, it indicates that there is a problem with the master and slave nodes. send a text message to alert you.

For more information, see my other article: nagios monitoring mysql master-slave Replication