MySQL Master-Slave replication (Master-slave) practice, mysqlmaster-Slave

MySQL Master-Slave replication (Master-slave) practice, mysqlmaster-Slave

The master-slave replication function provided by the MySQL database can easily implement multiple automatic data backups and expand the database. Multiple data backups not only enhance data security, but also further improve database load performance by implementing read/write splitting.

This section describes a model for master-slave replication and read/write separation between multiple databases (Source Network ):

In a master-slave database system, multiple slave servers asynchronously update changes in the master database, the Business Server performs write or related database modification operations on the master server, while the read operations are performed on the slave servers. If multiple slave servers or multiple master servers are configured and the corresponding Server Load balancer problems are involved, the specific technical details of Server Load balancer have not been studied, today, we will simply implement the master-slave replication function.

The implementation principle of Mysql master-slave replication is roughly as follows (Source Network ):

Data replication between MySQL instances is based on binary log files ). Once binary logs are enabled for a MySQL database, it acts as the master. All operations in its database are recorded in binary logs as "events, other databases use slave to communicate with the master server through an I/O thread and monitor the changes in the binary log files of the master. If the binary log files of the master server change, the changes will be copied to your own relay logs, and an SQL thread of slave will execute the related "events" into its own database, in order to achieve consistency between the slave database and the master database, master-slave replication is achieved.

Configuration required for MySQL master-slave replication:

• Master Server:
  • Enable binary log
  • Configure a unique server-id
  • Obtain the file name and location of the master binary log
  • Create a user account for slave and master communications
• Slave Server:
  • Configure a unique server-id
• Use the user account assigned by the master to read the binary log of the master.
• Enable the slave Service

The specific implementation process is as follows:

I. preparations:

1. The Master/Slave database versions are best consistent.

2. Data Consistency in the master and slave Databases

Primary Database: 182.92.172.80/linux

Slave Database: 123.57.44.85/linux

2. master database modification:

1. Modify mysql Configuration

Find the configuration file my. cnf (or my. ini) of the primary database, insert the following two lines in the/etc/mysql/my. cnf section in [mysqld:

[Mysqld] log-bin = mysql-bin # enable binary log server-id = 1 # Set server-id

2. Restart mysql to create a user account for synchronization.

Open mysql session shell> mysql-hlocalhost-uname-ppassword

Create a user and authorize: User: rel1 password: slavepass

Mysql> create user 'repl' @ '123. 57.44.85 'identified BY 'slavepass'; # create a user mysql> grant replication slave on *. * TO 'repl' @ '123. 57.44.85 '; # assign permissions mysql> flush privileges; # refresh Permissions

3. View master Status, record binary file name (mysql-bin.000003) and location (73 ):

mysql > SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003 | 73       | test         | manual,mysql     |+------------------+----------+--------------+------------------+

Ii. Modify the slave server slave:

1. Modify mysql Configuration

Find the my. cnf configuration file and add the server-id

[Mysqld] server-id = 2 # Set server-id, which must be unique

2. Restart mysql, open the mysql session, and execute the SQL statement synchronization.(The Host Name, login creden, and binary file name and location are required ):

mysql> CHANGE MASTER TO    ->     MASTER_HOST='182.92.172.80',    ->     MASTER_USER='rep1',    ->     MASTER_PASSWORD='slavepass',    ->     MASTER_LOG_FILE='mysql-bin.000003',    ->     MASTER_LOG_POS=73;

3. Start the slave synchronization process:

mysql>start slave;

4. view the slave status:

mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 182.92.172.80                  Master_User: rep1                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000013          Read_Master_Log_Pos: 11662               Relay_Log_File: mysqld-relay-bin.000022                Relay_Log_Pos: 11765        Relay_Master_Log_File: mysql-bin.000013             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:         ...

When both Slave_IO_Running and Slave_ SQL _Running are YES, the master-slave synchronization settings are successful. Next, we can perform some verification. For example, We can insert a data entry into a table in the test database of the master database, check whether new data exists in the same data table of the test database of slave to verify whether the master-slave replication function is valid. You can also disable slave (mysql> stop slave;) and then modify the master, check whether the Server Load balancer instance has been modified accordingly. After the Server Load balancer instance is stopped, the master node's modifications are not synchronized to the Server Load balancer instance.

Other parameters that can be used:

After enabling the binary log, the master records all the operations on the tables in all databases by default. You can configure to specify operations that only record the specified database or even the specified table, in the mysql configuration file [mysqld], you can add and modify the following options:

# Which databases are not synchronized binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema # Which databases are synchronized only. In addition, other binlog-do-db = game not synchronized

For example, you can see that only the test database is recorded and the manual and mysql databases are ignored.

References:

MySQL official manual

Set master-slave synchronization for MySQL Databases

MySQL Master-Slave replication and read/write splitting (MySQL-Proxy) practices

CentOS MySQL dual-machine hot standby configuration