Deploying MySQL master-Slave synchronization

Deploying MySQL master-Slave synchronization
First, what is MySQL master-slave synchronization
Primary: The database server that is being accessed by the client, known as the primary library server.
From: The database server that automatically synchronizes the data on the main library, is called from the library server.

Second, configure MySQL master-slave synchronization
2.1 Topology Diagram
Database Server 192.168.4.51 Master Library
Database server 192.168.4.52 do from library

2.2 Environment Preparation
Before master-slave synchronization is not configured, make sure that you have the data on the main library from the library.
disabling SELinux]# Setenforce 0
Shut down the Firewall service]# systemctl stop Firewalld
Physical connection Normal]# Ping-c 2 192.168.4.51/52
The database runs correctly, and management can log on from native

2.3 Configuring MySQL Master-Slave synchronization
+++2.3.1 Configuring the Main library
A CREATE user authorization
b Enable Binlog Log
C Viewing the Binlog log information being used

+++.3.2 Configuration from Library
A verifying the user authorization of the main library
B designation server_id
C database administrator native logon, specifying information for the primary database server
Mysql> Change Master to
master_host= "Main Library IP Address",
master_user=, "Master Library authorized user name",
-master_password= "Authorized user password",
master_log_file= "Main Library binlog log file name",
Master_log_pos=binlog log file offset;
D Start the slave process
E View process status information

Related commands
Mysql> Show slave status; # Show status information from library
Mysql> Show master status; #显示本机的binlog日志文件信息
Mysql> show Processlist; #查看当前数据库服务器上正在执行的程序
mysql> start slave; #启动slave process
mysql> stop Slave; #停止slave process

2.4 Testing the master-slave synchronization configuration on the client
2.4.1 When adding access data to the primary library server, use the connected user
2.4.2 Client uses an authorized user of the main library, connects to the main library server, and the repository table inserts records
2.4.3 from the library, use the admin login to see if there are library table records and authorized users like the main library

2.4.4 client uses an authorized user of the main library, connects from the library server, and can see the newly created library tables and records
+++++++++++++++++++++++++++++++
Third, MySQL master-slave synchronization of the working principle
Files from the library database directory:
Master.info Record the main library information
Host name-relay-bin. XXXXXX relay log files to record SQL commands executed on the main library
Hostname-relay-bin.index index file, which records the currently existing trunk log file
Relay-log.info relay log files that log information that is currently in use

What is the role of the library IO thread and the SQL thread?
The IO thread logs the SQL commands in the main library Binlog log to the local relay log file
The SQL thread executes the SQL command in the native relay log file and writes the data into the machine.

IO thread error Reason: Failed to connect to main library from library (ping Grant Firewalld SELinux)
Log information error (log name offset) from the library that specifies the main library

Last_io_error: Error message

To modify a step:
mysql> stop Slave;
mysql> change Master to option = "value";
mysql> start slave;

SQL thread error Reason: Execute the SQL command in the native relay log file, use the library or the table does not exist in this machine.
Last_sql_error: Error message

Set the data from the library to be temporarily out of sync with the main library?
Stop the slave process from the library
mysql> stop Slave;

Restore from library to stand-alone database server?
]# Rm-rf/var/lib/mysql/master.info
]# systemctl Restart Mysqld
]# RM-RF host name-relay-bin. XXXXXX Host name-relay-bin.index Relay-log.info

MySQL master and slave synchronous structure mode
One Master one from
A master more from
Master slave from
Main main structure (also known as Inter-Master slave)

V. MySQL master-Slave synchronization Common configuration parameters
Parameters used by the primary library server in configuration file my.cnf
] #vim/etc/my.cnf
[Mysqld]
List of binlog_do_db= library names #只允许同步库Binlog_Ignore_DB = List of library names #只不允许同步库
]# systemctl Restart Mysqld

Parameters used in configuration file my.cnf from the library server
]# vim/etc/my.cnf
[Mysqld]
Log_slave_updates
#级联复制
relay_log= Trunk Log file name
List of replicate_do_db= library names #只同步的库
List of replicate_ignore_db= library names #只不同步的库
: Wq
]# systemctl Restart Mysqld

Configure MySQL master slave structure
Main Library 192.168.4.51
From library 192.168.4.52 (do 51 hosts from library)
From library 192.168.4.53 (do 53 hosts from library)
Requirement: Client access to Main Library 51 o'clock Create library table records can be seen on both 52 and 53 database servers

Configuration steps:
First, the Environment preparation
Before master-slave synchronization is not configured, make sure that you have the data on the main library from the library.
disabling SELinux]# Setenforce 0
Shut down the Firewall service]# systemctl stop Firewalld
Physical connection Normal]# Ping-c 2 192.168.4.51/52
The database runs correctly, and management can log on from native
Second, configure master-slave synchronization
2.1 Configuring the Main Library 51
User authorization
Enable Binlog logging
To view the log information being used

2.2 Configuration from Library 52
User authorization
Enable Binlog logging, specify SERVER_ID, and allow cascade replication
To view the log information being used
Verifying authorized users of the main library
Administrator login Specifies the main library information
Start the slave process
View process status information

2.3 Configuration from Library 53
Verifying authorized users of the main library
Specify server_id
Administrator login Specifies the main library information
Start the slave process
View process status information

Third, client authentication configuration
3.1 Users authorized to access the Gamedb library on the main library
3.2 Clients use authorized users to connect to the main library, build libraries, tables, insert records
3.3 Clients can also see new library table records on the main library when they connect to 2 from the library using authorized users

Vi. MySQL master-slave synchronous replication mode
Asynchronous replication
Full synchronous replication
Semi-synchronous replication

See if modules can be loaded dynamically
Mysql> Show variables like "have_dynamic_loading";

Modules installed in the main library
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

Modules installed from the library
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

View the table under the System library for the module to be installed successfully
Mysql>
SELECT Plugin_name, Plugin_status
From INFORMATION_SCHEMA. PLUGINS
WHERE
Plugin_name like '%semi% ';

Enable semi-synchronous replication mode
Main Library
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

From the Library
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

View whether semi-synchronous mode is enabled
Mysql> Show variables like "Rpl_semisync%_enabled";

Modify the configuration file/etc/my.cnf the installation module and enabled mode to take effect permanently.

Main Library
Vim/etc/my.cnf
[Mysqld]
Plugin-load=rpl_semi_sync_master=semisync_master.so
Rpl_semi_sync_master_enabled=1
: Wq

From the Library
Vim/etc/my.cnf
[Mysqld]
Plugin-load=rpl_semi_sync_slave=semisync_slave.so
Rpl_semi_sync_slave_enabled=1
: Wq

Both the decision and the
Vim/etc/my.cnf
[Mysqld]
Plugin-load = "Rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
: Wq

Deploying MySQL master-Slave synchronization