MySQL master-slave replication practice

Time:2021-9-17

Basic information

Primary: 192.168.0.1 root pwd1
From: 192.168.0.2 root pwd2

backups

Copy / etc / MySQL / mysql.conf.d/mysqld.cnf of the two servers to / etc / MySQL / mysql.conf.d/mysqld.cnf.bak for data recovery

Master server configuration
# bind-address           = 127.0.0.1
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

Comment out bind address to 127.0.0.1, allow remote connection, and open the comment of server ID and log binaries

mysql> show master status;

Get the file information and position information of MySQL bin, for example: MySQL bin.00000 1 9937

grant replication slave on *.* to 'repl'@'192.168.0.2' identified by 'replpwd';

Create a login user from the library and grant slave permission

Configure from server
server-id               = 2

replicate-do-db=buildings

Open the comment of server ID and manually modify it, which is different from the primary server
I want to configure the replication of a single database, so I configure a replicate do DB I need

change master to master_host='192.168.0.1', master_user='repl', master_password='replpwd', master_log_file='mysql-bin.000001', master_log_pos=9937;
mysql> show slave status \G;   Slave_ IO_ Running and slave_ SQL_ If the running value is yes, it means success

Note that in the configuration, you can set which libraries and tables to copy only, and which libraries and tables to ignore. However, replicate do dB and replicate ignore DB can only exist, otherwise they will not take effect

Common commands
mysql>  stop slave; //  Stop replication
mysql>  reset slave; //  Reset replication
mysql>  start slave; //  Turn on replication
mysql>  show master status; //  Display main library status 
MySQL > show slave status \ g // the slave status is displayed

Reference documents

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Recommended Today

Seven Python code review tools recommended

althoughPythonLanguage is one of the most flexible development languages at present, but developers often abuse its flexibility and even violate relevant standards. So PythoncodeThe following common quality problems often occur: Some unused modules have been imported Function is missing arguments in various calls The appropriate format indentation is missing Missing appropriate spaces before and after […]