Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the realization principle of master-slave replication of Mysql database". In the daily operation, I believe that many people have doubts about the implementation principle of master-slave replication of Mysql database. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "the realization principle of master-slave replication of Mysql database". Next, please follow the editor to study!

The schematic diagram of Mysql master-slave replication is roughly as follows:

The basis of data replication between MySQL is based on binary log file (binary log file). Once binary log is enabled in a MySQL database, as master, all operations in the database will be recorded in the binary log in the way of "events". Other databases as slave keep communication with the master server through an I / O thread, and monitor the changes of binary log files in master. If a change is found in the master binary log file, the change will be copied to its own relay log, and then a SQL thread of slave will execute the relevant "events" into its own database, thus achieving the consistency between the slave database and the master database, that is, master-slave replication.

To implement the MySQL master-slave replication configuration requirements:

The main server: 1, turn on the database binary log function; 2, configure the database authentication unique service id;3, obtain the binary log file name and location of the master database; 4, create a user account on the master database for communication between the master database and the slave database, security management.

Slave server: 1, configure the unique service id;2 in the slave library, use the assigned user account to read the binary log of the master library; 3, enable the slave function for master-slave communication.

1. Preparatory work:

1. The version of the master-slave database had better be the same.

two。 Data consistency in master-slave database

Master database (master): 192.168.3.91 / CentOS Linux release 7.5.1804 (Core)

From database (slave): 192.168.3.218 / CentOS Linux release 7.5.1804 (Core)

Note: the master and slave here are all mariadb 5.5.56 installed through the yum source

# yum install mariadb-server.x86_64 mariadb.x86_64-y

/ / set mariadb service

# systemctl start mariadb.service & & systemctl enable mariadb.service

/ / set the password for the root account of mariadb database. By default, root users do not have a password.

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB

SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current

Password for the root user. If you've just installed MariaDB, and

You haven't set the root password yet, the password will be blank

So you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB

Root user without the proper authorisation.

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone

To log into MariaDB without having to have a user account created for

Them. This is intended only for testing, and to make the installation

Go a bit smoother. You should remove them before moving into a

Production environment.

Remove anonymous users? [Y/n] y

... Success!

Normally, root should only be allowed to connect from 'localhost'. This

Ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n

... Skipping.

By default, MariaDB comes with a database named 'test' that anyone can

Access. This is also intended only for testing, and should be removed

Before moving into a production environment.

Remove test database and access to it? [Y/n] n

... Skipping.

Reloading the privilege tables will ensure that all changes made so far

Will take effect immediately.

Reload privilege tables now? [Y/n] y

... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB

Installation should now be secure.

Thanks for using MariaDB!

2. Modify the main database master:

1. Modify mysql configuration

Find the configuration file my.cnf (or my.ini) for the main database, mine is in / etc/my.cnf, and insert the following two lines in the [mysqld] section:

# find /-name my.cnf

Default configuration

[mysqld] log-bin=mysql-bin # enable binary log server-id=1 # set server-id

Log-bin= "/ var/lib/mysql/" # sets the name of the generated log file

After modification:

# systemctl restart mariadb.service

two。 Restart mysql to create a user account for synchronization

# mysql-hlocalhost-uroot-ppassword

Create user and authorize: user: wxp, password: password

MariaDB [(none)] > CREATE USER 'wxp'@'192.168.3.218' IDENTIFIED BY' password';# create user

MariaDB [(none)] > GRANT REPLICATION SLAVE ON *. * TO 'wxp'@'192.168.3.218';# assign permissions

MariaDB [(none)] > flush privileges; # Refresh permissions

3. View the master status and record the binary file name (mysql-bin.000001) and location (492):

MariaDB [(none)] > SHOW MASTER STATUS

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 492 |

+-+

1 row in set (0.00 sec)

Second, modify from the server slave:

1. Modify mysql configuration

Also find the my.cnf configuration file and add server-id

# find /-name my.cnf

My.cnf default configuration

[mysqld] server-id=2 # sets server-id, which must be unique

Log-bin= "/ var/lib/mysql/" # sets the name of the generated log file

After modification:

# systemctl restart mariadb.service

two。 Restart mysql, open a mysql session, and execute synchronous SQL statements (requires host server hostname, login credentials, name and location of binaries):

# mysql-hlocalhost-uroot-ppassword

MariaDB [(none)] > CHANGE MASTER TO-> MASTER_HOST='192.168.3.91',-> MASTER_USER='wxp',-> MASTER_PASSWORD='password',-> MASTER_LOG_FILE='mysql-bin.000001',-> MASTER_LOG_POS=492

Here, the information is written directly into the database.

Mysql > select * from mysql.slave_master_info\ G

3. Start the slave synchronization process:

MariaDB [(none)] > start slave

4. View slave status:

MariaDB [(none)] > show slave status\ G

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.3.91

Master_User: wxp

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 492

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 529

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

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: 492

Relay_Log_Space: 825

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

1 row in set (0.00 sec)

ERROR: No query specified

When both Slave_IO_Running and Slave_SQL_Running are YES, the master-slave synchronization setting is successful. Then you can do some verification, such as inserting a piece of data into a table in the test database of the master master database, checking whether there is any new data in the same data table in the test library of slave to verify whether the master-slave replication function is valid, and you can also close slave (MariaDB [(none)] > stop slave). Then modify master to see if slave is modified accordingly (after stopping slave, master changes will not be synchronized to slave), and you can complete the verification of master-slave replication function.

5. Test and operate Master database

MariaDB [(none)] > use test

Database changed

MariaDB [test] > create table T1 (Name varchar (18))

Query OK, 0 rows affected (0.03 sec)

MariaDB [test] > insert into T1 (Name) values ('wxp')

Query OK, 1 row affected (0.01sec)

MariaDB [test] > select * from T1

+-+

| | Name |

+-+

| | wxp |

+-+

1 row in set (0.00 sec)

Check whether the test library has data synchronization on slave.

# mysql-hlocalhost-uroot-ppassword

MariaDB [(none)] > use test

MariaDB [test] > show tables

+-+

| | Tables_in_test |

+-+

| | T1 |

+-+

1 row in set (0.00 sec)

MariaDB [test] > select * from T1

+-+

| | Name |

+-+

| | wxp |

+-+

1 row in set (0.00 sec)

6. Other relevant parameters that can be used:

When binary log is enabled in master, the operations of all tables in all libraries are recorded by default. You can specify that only the specified database or even the specified table operations can be recorded through configuration. You can add and modify the following options in "mysqld" of mysql configuration file:

# which databases are not synchronized

# vim / etc/my.cnf binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema

# systemctl restart mariadb.service

# only synchronize which databases, other than not synchronize binlog-do-db = wxp

# Log retention time

Expire_logs_days = 10

# controls the writing frequency of binlog. How many transaction writes per execution

# this parameter consumes a lot of performance, but can reduce the loss caused by MySQL crash

Sync_binlog = 5

# Log format, mixed is recommended

# statement saves SQL statement

# row saves impact record data

# combination of the first two kinds of mixed

Binlog_format = mixed

Operate on the slave database to set the reconnection timeout

# stop master-slave synchronization

Mysql > stop slave

# reconnect timeout when the connection is disconnected

Mysql > change master to master_connect_retry=50

# enable master-slave synchronization

Mysql > start slave

At this point, the study of "the implementation principle of master-slave replication of Mysql database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.