MySQL Replication (mysql5.7 master-slave copy and read-write separation)

Replication: Enables automatic replication of data between multiple database servers.

Strategy One: Method based on binary log file (binary journaled text) pieces

Implementation mechanism:

1. All write and update operations of the master server database are logged to bin log file as "event".

2. The slave servers reads the contents of the bin log file from master and executes the event logged in the bin log file locally on the slave server.

Slave server receives a full copy of the bin log file content, and by default executes all events recorded in it [configurable to execute only the specified tables, events, if you have special needs].

Each slave server holds a record containing the bin log file coordinates [coordinates refer to the file name of the log file on the master server and the location in the log file].

Description: In the master-slave Replication Service Group, master and each slave set a unique ID to uniquely identify the MySQL instance.

Each slave server must configure the host name, log file coordinates, and so on for the master server to synchronize the required information!

Configuration implementation:

1. Configure Master server

1.1 Turn off MySQL service

1.2 Add the configuration information in the My.ini configuration file as follows:

[Mysqld]

Log-bin=mysql-bin #开启bin log File option with Mysql-bin as the logfile prefix

Server-id=1 #把 master server ID is set to 1

1.3 Restart MySQL service to

2. Get bin log file coordinates on master server

2.1 Login client, use the command show Master status to view the coordinate file information

2.1.1 If you do not configure Master server, return empty

2.1.2 If configured, use the "flush TABLES with READ LOCK;" Statement to refresh the table (all data in the cache is flushed back to the data file) while prohibiting all MySQL write operations to ensure data integrity.

Then call the command show Master status, which will display the file name, position within the file, binlog_do_db, binlog_ignore_db four information.

We record four items of information, and when configuring Salve server, file name and position are two required information.

　　

3. Create a connection user for the master and slave server

mysql> CREATE USER ' repl ' @ '%.mydomain.com ' identified by ' slavepass ';
Mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ '%.mydomain.com ';

4. Configuring Slave server

4.1 Release Read lock

UNLOCK TABLES;

4.2 Configuring the slave in the My.ini file Server-id

[Mysqld]

server-id=2;

4.3 Configuring the necessary information to connect to master server on slave server

Mysql> Change MASTER to
Master_host= ' Master_host_name ',
Master_user= ' Replication_user_name ',
Master_password= ' Replication_password ',
Master_log_file= ' Recorded_log_file_name ',
Master_log_pos=recorded_log_position;

The above configuration conditions: The master server is new, is a set of empty databases, does not contain user-created data.

If your database already has data, the slave server will only synchronize new data after the master-slave configuration. At this point, the configuration steps need to make the following changes:

Foreword: If the master server has data, we need to copy this data to each slave server.

1. Create database snapshot for master server (data Backup)

1.1 Create a backup using the Mysqldump tool:

Mysqldump--all-databases > Dbdump.db

Additional options are available:

--master-data [Change Master to statement is automatically appended to the backup file to enable the replication process on slave server and does not need to lock the master]

--ignore-table [Set tables without backing up the database]

--databases [Set the database to be backed up]

1.2 Backing up using the database raw files

2. Import the master backup data to the slave server before starting slave replication

1.1 Backup is generated by the Mysqldump tool

1) Start slave server by using the--skip-slave-start option to prevent slave from starting replication

2) Import Dump File:mysql < dbdump.db

1.2 Backup from the original file

　　　　

MySQL Replication (mysql5.7 master-slave copy and read-write separation)