17th MySQL Master-slave configuration

17.1 MySQL Master-slave introduction

MySQL master-slave is also called replication, AB replication. Simply said that A and b two machines from the back, on a to write the data, the other B will follow the writing data, the two data in real-time synchronization.
MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave.

Master-Slave process

To log change operations to Binlog
From synchronizing the main binlog event (SQL statement) to the native and logging in Relaylog
Execute sequentially from the SQL statements inside the Relaylog
Description: The process has three threads, and the master has a log dump thread that is used to pass binlog to and from the I/O thread, with two threads on it, where I/O threads are used to synchronize the main binlog and generate Relaylog. Another SQL thread is used to put the SQL statements inside the relaylog into the ground.

Working principle

Mark

Application environment

Back up important data
Share the main library data read pressure
17.2 preparatory work

Install and start the MySQL service.

17.3 Configuring the Primary server

Edit the configuration file by adding the following parameters:

[Email protected] ~]# VIM/ETC/MY.CNF
......
server-id=132
#自定义
Log_bin=centos-01linux1
#指定log前缀
Restart the MySQL service when editing is complete:

[Email protected] ~]#/etc/init.d/mysqld restart
Shutting down MySQL ... success!
Starting MySQL ......... ..... success!
To view the MySQL library file:

[Email protected] ~]# ls-lt/data/mysql/
......
-RW-RW----1 MySQL mysql 20 August 15:52 centos-01linux1.index
-RW-RW----1 MySQL mysql 120 August 15:52 centos-01linux1.000001
......
Description: A two binary file prefixed with CENTOS-01LINUX1 is generated after a reboot.

Create a new database to prepare for the experiment:

[Email protected] ~]# cd/data/mysql/

Back up a database:
[Email protected] mysql]# mysqldump-uroot-p123456 zrlog >/tmp/zrlog.sql

Create a new database:
[Email protected] mysql]# mysql-uroot-p123456-e "CREATE Database Centos-01test"

Restore the backed up data to the newly created database:
[Email protected] mysql]# mysql-uroot-p123456 Centos-01test </tmp/zrlog.sql
Create a user to synchronize data with:

[Email protected] mysql]# mysql-uroot-p123456
Welcome to the MySQL Monitor.
mysql> grant replication Slave on . to ' repl ' @ ' 192.168.8.130 ' identified by ' 123456 ';
Query OK, 0 rows affected (0.01 sec)
#IP为 the "from" IP

Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.12 sec)
#锁定数据表 (The purpose is to temporarily make it unable to continue writing, keeping the existing state for synchronization)

Mysql> Show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+-------------------+----------+--------------+------------------+-------------------+
| centos-01linux1.000001 | 10844 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)
#记住file和position (used when setting up master-slave synchronization)
Mysql> quit
Bye
To back up all databases in the main library:

[Email protected] mysql]# mysqldump-uroot-p123456 centos-01 >/tmp/centos-01.sql
[Email protected] mysql]# mysqldump-uroot-p123456 centos-012 >/tmp/centos-012.sql
17.4 configuration from the server

To edit a configuration file:

[Email protected] ~]# VIM/ETC/MY.CNF
......
server-id=130
......

[Email protected] ~]#/etc/init.d/mysqld restart
Shutting down MySQL ... success!
Starting MySQL ......... .............. success!
Description: The Server-id to be different from the Server-id of the Lord.

After the configuration is complete, the data that is backed up in the master is sent to from:

[Email protected] ~]# SCP 192.168.8.132:/tmp/*.sql/tmp/
The authenticity of host ' 192.168.8.132 (192.168.8.132) ' can ' t be established.
ECDSA key fingerprint is 78:22:19:9e:d5:4a:9d:cb:71:54:d7:c0:9a:13:18:9c.
Is you sure want to continue connecting (yes/no)? Yes
warning:permanently added ' 192.168.8.132 ' (ECDSA) to the list of known hosts.
[email protected] ' s password:
Centos-012.sql 100% 1259 1.2kb/s 00:00
Centos-01.sql 100% 1258 1.2kb/s 00:00
Zrlog.sql 100% 9864 9.6kb/s 00:00
To create a library:

[Email protected] ~]# Mysql-uroot
Welcome to the MySQL Monitor.
mysql> CREATE DATABASE centos-01;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE centos-012;
Query OK, 1 row Affected (0.00 sec)

mysql> CREATE DATABASE Zrlog;
Query OK, 1 row Affected (0.00 sec)
Mysql> quit
Bye
To recover a database:

[Email protected] ~]# Mysql-uroot centos-01 </tmp/centos-01.sql
[Email protected] ~]# Mysql-uroot centos-012 </tmp/centos-012.sql
[Email protected] ~]# Mysql-uroot Zrlog </tmp/zrlog.sql
Note: This process should ensure the consistency of the contents of the master-slave database.

Implement Master-Slave synchronization

[Email protected] ~]# Mysql-uroot
Welcome to the MySQL Monitor.
mysql> stop Slave;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> Change Master to master_host= ' 192.168.8.132 ', master_user= ' repl ', master_password= ' 123456 ', master_log_file = ' centos-01linux1.000001 ', master_log_pos=10844;
Query OK, 0 rows affected, 2 warnings (0.46 sec)
#IP为主的IP; file and Pos are the main filename and position respectively.

To detect the success of master-slave construction:
mysql> start slave;
Query OK, 0 rows affected (0.22 sec)

Mysql> Show Slave Status\g
Slave_io_running:yes
Slave_sql_running:yes
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
When the above operation is complete, unlock the table of the main library (operation in the Lord):

[Email protected] mysql]# mysql-uroot-p123456
Welcome to the MySQL Monitor.
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)
The master-slave configuration is completed!!!

17.5 Test Master

Parameter introduction

Primary server:
Binlog-do-db= only the specified libraries are synchronized
binlog-ignore-db= ignores the specified library

From the server:
replicate_do_db= synchronizing the specified library
replicate_ignore_db= ignores the specified library
replicate_do_table= Synchronizing a specified table
Replicate_ignore_table= ignores the specified table

Replicate_wild_do_table= such as aming.%, wildcard characters supported
replicate_wild_ignore_table=
Note: Use the parameter "Replicatewild" as much as possible when configuring from the server to make the match more accurate and improve the performance of the use.

Test

Primary server:

Mysql> Show tables;
+---------------------------+
| Tables_in_centos-01test |
+---------------------------+
| Columns_priv |
| db |
| Event |
+---------------------------+

To delete a table:
mysql> drop table db;
Mysql> Show tables;
+---------------------------+
| Tables_in_centos-01test |
+---------------------------+
| Columns_priv |
| Event |
+---------------------------+
From the server:

Before the primary server deletes the table:
Mysql> Show tables;
+---------------------------+
| Tables_in_centos-01test |
+---------------------------+
| Columns_priv |
| db |
| Event |
+---------------------------+
After the primary server deletes the table:
Mysql> Show tables;
+---------------------------+
| Tables_in_centos-01test |
+---------------------------+
| Columns_priv |
| Event |
+---------------------------+
That is, the master-slave configuration succeeds!

17th MySQL Master-slave configuration