MySQL master-slave environment setup configuration

= Start =

Reason:

Work needs, learning needs.

Body:

MySQL master/slave architecture:

One MySQL instance acts as the master database and receives read/write requests. Another or more MySQL instances synchronize data from the master database to the slave database through the MySQL replication mechanism. The slave database is set to read-only, prevents master/slave data inconsistency.

Benefits of the master-slave architecture:

If a problem occurs on the master server, you can quickly switch to the slave server to provide services;
Queries can be executed on the server to reduce the pressure on the master server;
Backup can be performed on the slave server to avoid affecting the performance of the master server during the backup.
Limitations of the master-slave architecture:

Because MySQL implements asynchronous replication, there is a certain difference (latency) between the data on the master and slave servers. Data with high real-time requirements still needs to be obtained from the master server.

Master-slave setup steps:

Step

Master)

Slave Database (Slave)

1 
Modify the my. cnf configuration file of the master database, and then restart it to take effect.

[Mysqld]
 
# As the master database
# Start a binary file (*)
Log-bin = mysql-bin
# Server ID (*)
Server-id = 1
 
# Databases to be backed up, multiple writes and multiple lines
Binlog-do-db = orders
# Databases that do not need to be backed up, multiple write lines
Binlog-ignore-db = mysql
Modify the configuration file from the library my. cnf, and then restart it to take effect.

[Mysqld]
 
# Serve as the Slave Database (if there are multiple slave databases, the server-IDs in their my. cnf must be different)
Server-id = 2
 
Replicate-do-db = bash # Only Copy a database and write multiple rows
Replicate-ignore-db = mysql # Do not copy a database, multiple write lines
Replicate-ignore-db = test
Replicate-ignore-db = information_schema
2. Create an account (repl) used to connect to the master database from the slave database)
Mysql> GRANTREPLICATIONSLAVEON *. * TO 'repl' @ 'slave _ IP' IDENTIFIEDBY 'password ';
Mysql> flushprivileges;
Log on to the Master using the newly created repl account on the host where the Slave is located, and test whether the account used for replication can be connected.
Mysql-h master_ip-u repl-p
3. Record synchronization points
Mysql> FLUSHTABLESWITHREADLOCK; // if data already exists in the database to be synchronized, use this statement to lock the database.
Mysql> SHOWMASTERSTATUS; // view the Master status and record the File and Position information.
4 
Logical or physical backup of the master database data

Mysql> flushtableswithreadlock;
 
Shell> mysqldump-uroot-p -- all-databases -- master-data> backup. SQL
 
Shell> scp-C backup. sqluser @ slave_ip:/tmp/
 
Mysql> unlocktables;
5. Restore data from the slave database
Shell> mysql-uroot-p </tmp/backup. SQL
Shell> rm-rf/tmp/backup. SQL
6  
Log on to the slave database as the root user, and set information about the master database (host, port, user, etc)

Mysql> CHANGEMASTERTO MASTER_HOST = 'master _ IP', MASTER_USER = 'repl', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'MySQL-bin.000001 ', MASTER_LOG_POS = 107;
 
Mysql> STARTSLAVE; // start master-slave replication
 
Mysql> SHOWSLAVESTATUS \ G // view the slave status
7 If everything goes well, the operations on the Master will be synchronized to the Slave.
Create a table test_tb in the master database to simulate new data in the database.

Mysql> create table test_tb (id int, name varchar (30); check whether the test_tb table exists in the slave database.
Mysql> show tables;