CentOS 7 System configuration MySQL master-slave replication mode (Master-slave Replication)

MySQL master-slave replication is widely used in database backup, failover, data analysis and other occasions.
MySQL master-slave replication keeps track of all changes to the database (update, delete, and so on) in the binary log based on the master server. Therefore, to replicate, you must enable binary logging on the primary server. Receive updates from the server from the primary server that have been logged to the binary log. When a primary server is connected from a server, the primary server reads the last successful update from the log, receives updates from the server that occurred, and performs the same update on the local computer, and then waits for the primary server to notify the new update. Performing a backup from the server does not interfere with the primary server, and the primary server can continue to process updates during the backup process.

Test environment
master:192.168.10.201

slave:192.168.10.202

Port: 3306

Database: Test2

installing MySQL
Yum Install mariadb Mariadb-server
Systemctl Enable MARIADB
Service MARIADB Start

# Reset Root Password
Mysqladmin-u Root Password Abc@def
Master server Configuration

Primary server configuration file/etc/my. Cnf
[Mysqld]
Server-id=1
Binlog-do-db=test2
Relay-log=/var/lib/mysql/mysql-relay-bin
Relay-log-index=/var/lib/mysql/mysql-relay-bin.index
Log-error=/var/lib/mysql/mysql.err
Master-info-file=/var/lib/mysql/mysql-master.info
Relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
Log-bin=/var/lib/mysql/mysql-bin
Restart MySQL

Service MARIADB Restart
Give replication slave permissions
Mysql-uroot-p
GRANT REPLICATION SLAVE on *.* to ' slave_user ' @ '% ' identified by ' password ';
FLUSH privileges;
FLUSH TABLES with READ LOCK;
Show MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000002 | 469 |                  Test2 | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Note: Make a note of the Red section and use it later.

Backing up a database
In order to back up the database, you need to have a read lock for all tables in the database, and then a dump backup:

Mysqldump-u root-p--all-databases--master-data >/root/dbdump.db
After the backup is complete, you can unlock it with the following command:

Mysql-uroot-p
UNLOCK TABLES;
Configure from server

Restore Database
Mysql-u Root-p </root/dbdump.db

/etc/my from the server configuration file. Cnf
[Mysqld]
server-id=2
Replicate-do-db=test2
Relay-log=/var/lib/mysql/mysql-relay-bin
Relay-log-index=/var/lib/mysql/mysql-relay-bin.index
Log-error=/var/lib/mysql/mysql.err
Master-info-file=/var/lib/mysql/mysql-master.info
Relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
Log-bin=/var/lib/mysql/mysql-bin
Restart MySQL

Service MARIADB Restart
Connecting to the primary server
Mysql-uroot-p
Stop slave;
Change MASTER to master_host= ' 192.168.10.201 ', master_user= ' slave_user ', master_password= ' PASSWORD ', master_log_file = ' mysql-bin.000002 ', master_log_pos=469;
Start slave;
Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event master_host:192.168.10.201
Master_user:slave_user
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000002
read_master_log_pos:469
relay_log_file:mysql-relay-bin.000004
relay_log_pos:529
relay_master_log_file:mysql-bin.000002
Slave_IO_Running:YesSlave_SQL_Running:Yes replicate_do_db:test2 replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:469
relay_log_space:1107
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
Master_server_id:1
Verify

Primary server
Mysql-uroot-p
Drop test2;
Create Database test2;
Use Test2;
CREATE TABLE EMP (c int);
INSERT into EMP (c) VALUES (10);
From the server
On the server, you should be able to see the same changes.

Expand your knowledge

GRANT REPLICATION SLAVE
The REPLICATION SLAVE privilege should is granted to accounts this are used by SLAVE servers to connect to the current Ser Ver as their master. Without this privilege, the slave cannot request updates which have been to made on the master server.

RULES for READ LOCK
The session is holds the lock can read the table (but not write it).

Multiple sessions can acquire a READ lock for the table at the same T ime.

Other sessions can read the table without explicitly acquiring a read lock.

RULES for WRITE LOCK
The session that holds the lock can read and write the table.

Only the "session" that holds the lock can access the table. No other sessions can access it until the lock is released.

The lock requests for the table is held with the other sessions blocks while the WRITE lock.