Shulou(Shulou.com)06/01 Report--

Deploy mysql master-slave synchronization

What is mysql master-slave synchronization

Master: the database server being accessed by the client is called the main library server.

Slave: the database server that automatically synchronizes data on the master database is called the slave server.

Configure mysql master-slave synchronization

2.1 Topology Diagram

Database server 192.168.4.51 as the main library

Database server 192.168.4.52 as slave library

2.2 Environmental preparation

Before master-slave synchronization is not configured, make sure that there is data on the master database on the slave database.

Disable selinux] # setenforce 0

Turn off firewall services] # systemctl stop firewalld

Physical connection is normal] # ping-c 2 192.168.4.51Pue 52

The database is running normally, and the management can be logged in locally.

2.3 configure mysql master-slave synchronization

+ + 2.3.1 configure the main library

A create user authorization

B enable binlog Log

C View binlog log information in use

+ .3.2 configure slave library

A verify the user authorization of the main library

B specify server_id

C database administrator log in locally and specify the information of the primary database server

Mysql > change master to

-> master_host= "main library ip address"

-> master_user= "main library authorization 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 command

Mysql > show slave status; # displays slave library status information

Mysql > show master status; # displays the local binlog log file information

Mysql > show processlist; # View the programs that are executing on the current database server

Mysql > start slave; # start the slave process

Mysql > stop slave; # stop the slave process

2.4 Test the master-slave synchronization configuration on the client side

2.4.1 when adding access data on the main library server, use the connection user

2.4.2 the client uses the authorized user of the main library to connect to the main library server and insert records into the database table.

2.4.3 on the slave machine, use the administrative login to check whether there are the same database table records and authorized users as the master database.

2.4.4 the client uses the authorized user of the master library to connect to the slave library server, and you can also see the newly created library tables and records

+ +

Third, the working principle of master-slave synchronization of mysql

From the files in the library database directory:

Master.info records the main database information

Hostname-relay-bin.XXXXXX relay log file to record sql commands executed on the main library

Hostname-relay-bin.index index file, which records the existing relay log file

Relay-log.info relay log file, which records the relay log information currently in use

What is the role of IO threads and SQL threads from the library?

The IO thread records the sql commands in the binlog log of the main library to the local relay log file.

The SQL thread executes the sql command in the native relay log file and writes the data to the local machine.

IO thread error reason: failed to connect to the master library from the slave library (ping grant firewalld selinux)

Error specifying log information for master library from slave library (log name offset)

Last_IO_Error: error message

Modify the steps:

Mysql > stop slave

Mysql > change master to option = "value"

Mysql > start slave

SQL thread error reason: execute the sql command in the local relay log file, using the library or table does not exist on the machine.

Last_SQL_Error: error message

Set up the slave database to temporarily not synchronize the data of the master database?

Stop the slave process on the slave library

Mysql > stop slave

Restore from the library to a stand-alone database server?

] # rm-rf / var/lib/mysql/master.info

] # systemctl restart mysqld

] # rm-rf hostname-relay-bin.XXXXXX hostname-relay-bin.index relay-log.info

Fourth, mysql master-slave synchronization structure mode

One master and one slave

One master and many followers

Master and subordinate

Master structure (also known as mutual master and slave)

Fifth, mysql master-slave synchronization common configuration parameters

Parameters used by the main library server in the configuration file my.cnf

] # vim / etc/my.cnf

[mysqld]

Binlog_do_db= library name list # only allow synchronization of library Binlog_Ignore_DB= library name list # only do not allow synchronization of library

] # systemctl restart mysqld

Parameters used by the slave library server in the configuration file my.cnf

] # vim / etc/my.cnf

[mysqld]

Log_slave_updates

# Cascade replication

Relay_log= Relay Log File name

Replicate_do_db= library name list # synchronized libraries only

Replicate_ignore_db= library name list # only libraries that are out of sync

: wq

] # systemctl restart mysqld

Configure mysql master-slave structure

Main library 192.168.4.51

Slave library 192.168.4.52 (as 51 host slave library)

Slave library 192.168.4.53 (do 53 host slave library)

Requirement: when the client accesses the main library 51, the database table records can be seen in both 52 and 53 database servers.

Configuration steps:

I. Environmental preparation

Before master-slave synchronization is not configured, make sure that there is data on the master database on the slave database.

Disable selinux] # setenforce 0

Turn off firewall services] # systemctl stop firewalld

Physical connection is normal] # ping-c 2 192.168.4.51Pue 52

The database is running normally, and the management can be logged in locally.

Second, configure master-slave synchronization

2.1 configure the main library 51

User authorization

Enable binlog Log

View log information in use

2.2 configuration slave 52

User authorization

Enable binlog logging, specify server_id, and allow cascading replication

View log information in use

Verify the authorized user of the main library

The administrator logs in to specify the main library information

Start the slave process

View process status information

2.3 configure slave library 53

Verify the authorized user of the main library

Specify server_id

The administrator logs in to specify the main library information

Start the slave process

View process status information

The client verifies the configuration

3.1 users authorized to access the gamedb library on the main library

3.2 the client uses authorized users to connect to the main library, build libraries, tables, and insert records

3.3 when the client uses authorized users to connect to two slave libraries, you can also see the new database table records on the master database.

VI. Mysql master-slave synchronous replication mode

Asynchronous replication

Full synchronous replication

Semi-synchronous replication

Check to 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'

Check the table under the system library to see if the module has been 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

Slave library

Mysql > SET GLOBAL rpl_semi_sync_slave_enabled = 1

Check whether semi-synchronous replication mode is enabled

Mysql > show variables like "rpl_semisync%_enabled"

Modify the configuration file / etc/my.cnf to make the installation module and enabled mode permanent.

Main library

Vim / etc/my.cnf

[mysqld]

Plugin-load=rpl_semi_sync_master=semisync_master.so

Rpl_semi_sync_master_enabled=1

: wq

Slave library

Vim / etc/my.cnf

[mysqld]

Plugin-load=rpl_semi_sync_slave=semisync_slave.so

Rpl_semi_sync_slave_enabled=1

: wq

Be both a master and a follower

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.