MySQL master/slave replication
MySQL primary/secondary replication
MySQL primary/secondary replication process
MySQL multi-instance database construction
Start and stop the MySQL multi-instance database
MySQL has one master and many slaves architecture
Enable binary logging in MySQL dual-master dual-slave configuration
MySQL one master, multiple secondary environment restart and MySQL two master, two secondary data replication verification
MySQL multi-instance database configuration
Reset the primary/secondary status of MySQL dual Primary/secondary
MySQL primary/secondary replication
If the database has only one server, then the single point of problem is relatively easy to occur. If the server access responder crashes, then the server cannot be used. If the hard disk fails, the entire data will be lost, which will be a major security incident.
To avoid data loss, at least two or more servers are prepared and deployed on different servers for data replication. If one server fails, the other servers work.
MySQL provides the master/slave replication function to improve service availability and data security.
In master-slave replication, a server is divided into a master server and a slave server. The master server is responsible for reading and writing data, while the slave server is only responsible for reading data. The master is the master and the slave is the slave.
MySQL master-slave replication architecture
MySQL has one master and many slaves architecture
If the binary log on the master server changes, an I/O Thread requests the master binary log.
At the same time, the master server starts a dump Thread for each I/O Thread to send binary logs to it.
The slave server saves the received binary logs to its local trunk log file.
The salve slave server will start SQL Thread to read binary logs from the slave log and play them back locally to keep the data consistent with the primary server.
Finally, I/O threads and SQL threads go to sleep, waiting to be woken up the next time.
MySQL > master/slave replication
MySQL > create /data/3307, /data/3308, /data/3309, /data/3310 directory;
/usr/local/mysql-5.7.18/bin /usr/local/mysql-5.7.18/bin /usr/local/mysql-5.7.18/bin /usr/local/mysql-5.7.18/bin
. / mysqld -- -- the initialize - insecure -- basedir = / usr/local/mysql - 5.7.18 - datadir = / usr/local/mysql - 5.7.18 / data / 3307 - user = mysql. / mysqld -- -- the initialize - insecure - basedir = / usr/local/mysql - 5.7.18 - datadir = / usr/local/mysql - 5.7.18 / data / 3308 - user = mysql/mysqld -- the initialize - insecure - basedir = / usr/local/mysql - 5.7.18 - datadir = / usr/local/mysql - 5.7.18 / data / 3309 - user = mysql/mysqld - the initialize - insecure - basedir = / usr/local/mysql - 5.7.18 - datadir = / usr/local/mysql - 5.7.18 / data / 3310 - user = mysqlCopy the codeInitialize -insecure indicates that the random password of MySQL database root is not generated, that is, the root password is empty.
/data/3307, /data/3308, /data/3309, /data/3310 create a my.cnf file;
4. Configure the my.cnf files for the four MySQL database services
[client] port = 3307 socket = / usr/local/mysql - 5.7.18 / data / 3307 / mysql. The sock default - character - set = utf8 port = [mysqld] 3307 socket = / usr/local/mysql - 5.7.18 / data / 3307 / mysql. The sock datadir = / usr/local/mysql - 5.7.18 / data/log - error = 3307 / usr/local/mysql - 5.7.18 / data / 3307 / error log pid - file = / usr/local/mysql - 5.7.18 / data / 3307 / mysql. The pid character-set-server=utf8 lower_case_table_names=1 autocommit = 1Copy the codeAt this point, multiple database instances are set up;
Start and stop the MySQL multi-instance database
The MySQL multi-instance database is started
. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3307 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3308 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3309 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3310 / my CNF &Copy the code–defaults-file indicates the specified configuration file, & indicates the background startup.
The MySQL multi-instance database is shut down
Switch to the /usr/local/mysql-5.7.18/bin directory and run the mysqladmin command to shutdown
A:
. / mysqladmin uroot - p - S/usr/local/mysql - 5.7.18 / data / 3307 / mysql. The sock shutdownCopy the codeMethod 2:
/mysqladmin -uroot -p -p3307-h127.0.0.1 shutdownCopy the codeMethod 3: If you have accessed the MySQL command-line interface (CLI), run shutdown.
Exit the MySQL command line and run: exit
MySQL has one master and many slaves architecture
The Lord (3307) adds in
Log-bin =mysql-bin # indicates that binary logging is enabled
Server-id =3307 # Indicates the server id, which must be unique
We add it from 3308
Server-id =3308 # Indicates the server id, which must be unique
We add it from 3309
Server-id =3309 # Indicates the server id, which must be unique
We add it from 3310
Server-id =3310 # Indicates the unique server id
MySQL is restarted in a primary and multiple secondary environment
Go to the /usr/local/mysql-5.7.18/bin directory and restart the four mysql services.
. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3307 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3308 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3309 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3310 / my CNF &Copy the codeMySQL has one primary and multiple secondary Settings
1. Create an account for replicating data on the primary server and authorize it:
grant replication slave on . to ‘copy’@’%’ identified by ‘123456’;
2. Run the reset master command to reset the master service status.
Select * from the primary server where the binary binlog file name and coordinates are used:
show master status;
Run the command on the slave server to set the master of the slave server
4, Reset the slave status: (You can check the slave status before resetting: show slave status)
stop slave;
reset slave;
5, change master to master_host=’192.168.199.128′,master_user=’copy’,
master_port=3307,master_password=’123456′,
master_log_file=’mysql-bin.000001′,master_log_pos=154;
6. Run the start slave command on the machine. (Start IO thread and SQL thread)
MySQL primary multiple secondary data replication validation
Check the status of the secondary server replication function
mysql> show slave status \G
If Slave_IO_Running and Slave_SQL_Running are YES, the status is normal.
Create databases, tables, and data on the primary server, and then check to see if it has been replicated on the secondary server
If the preceding operations are normal, the primary and secondary servers are configured.
Run the show binlog events in ‘mysql-bin.000001’ command to view the contents of the primary/secondary replication binlog log file.
Enable binary logging in MySQL dual-master dual-slave configuration
Master 3308 –> Slave 3310
3307 <– > 3308 indicates the master/slave mode
Two write nodes, and two read nodes under each write node;
Enable binary logging in MySQL dual-master dual-slave configuration
1. Configure the configuration file my.cnf for each MySQL server
3307
3308
3309
3310
2. As with one master with multiple slaves, all master configuration files are configured with binlog
3307
3308
3309
3310
log-bin=mysql-bin
Server-id = port of the instance (server-ID must be unique)
3. Add the following configuration to the my.cnf file on the first primary server (Master 3307) :
auto_increment_increment=2
auto_increment_offset=1
log-slave-updates
sync_binlog=1
Copy the code4. Add the following configuration to the my.cnf file of the second Master server 3308 :(Master 3308)
auto_increment_increment=2
auto_increment_offset=2
log-slave-updates
sync_binlog=1
Copy the codeConfiguration Item Description
Auto_increment_increment controls the increment of the primary key incremented by the increment increment increment increment increment used to prevent duplicate increments between Master and Master.
Auto_increment_offset = 1 set the starting value, it is set to 1, this Master auto_increment field produced by the numerical value is: 1, 3, 5, 7,… Such an odd number of ID
Note that the auto_increment_offset setting should not be the same for different master keys, otherwise it is easy to cause primary key conflicts, such as master1 offset=1, master2 offset=2, master3 offset=3
In dual master mode, the log-slave-updates configuration item must be configured. Otherwise, data will be updated on Master1 (3307), master2(3308) and Slave1 (3309), but not on Slave2 (3310)
Sync_binlog indicates that every time a transaction commits, MySQL will flush the binlog cache into the log file. The default value is 0, and the safest value is 1.
MySQL one master, multiple secondary environment restart and MySQL two master, two secondary data replication verification
. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3307 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3308 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3309 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql - 5.7.18 / data / 3310 / my CNF &Copy the codeMySQL dual primary and secondary data replication verification
To check the status of the secondary server replication function, run the following command:
mysql> show slave status \G
If Slave_IO_Running and Slave_SQL_Running are Yes, the replication function is properly configured.
After the dual-master dual-slave replication is configured:
-
If a master fails, it can switch to another master to write data, and the new master synchronizes data to its slave.
-
After the original master recovers the service, the new master synchronizes data to it, and then the new master synchronizes data to its slave. In this way, data synchronization and service unavailability will not occur.
MySQL multi-instance database configuration
-p is the specified password. If there is no password, -p can be omitted. -s is the specified sock file.
/mysql -uroot -p -p3307-h127.0.0.1 /mysql -uroot -p -p3307-h127.0.0.1
Mysql > alter mysql password;
alter user ‘root’@’localhost’ identified by ‘123456’; (123456 is the password we set)
3, authorize remote access, execute command :(so that the remote client can access)
grant all privileges on . to root@’%’ identified by ‘123456’;
The first of *.* represents all database names, and the second represents all database tables.
In %, root indicates the user name, % indicates the IP address, and % can also specify an IP address, such as root@localhost or [email protected]
4. Run the following command to refresh permissions:
flush privileges;
Perform the above steps at once:
alter user ‘root’@’localhost’ identified by ‘123456’;
grant all privileges on . to root@’%’ identified by ‘123456’;
flush privileges;
Reset the primary/secondary status of MySQL dual Primary/secondary
/mysql -uroot -p-p3307-h127.0.0.1./mysql -uroot -p-p3308-h127.0.0.1Copy the codeCreate an account on the primary server to replicate data and authorize:
grant replication slave on . to ‘copy’@’%’ identified by ‘123456’;
2. Stop the replication and refresh the binlog on the two primary servers.
Run the following command on MySQL:
stop slave; — Stop copying
reset slave; Reset the state of the slave server to its initial state
reset master; — Resets the state of the primary server to its initial state
3307 Machine execution:
reset master
Machine execution:
stop slave;
reset slave;
reset master;
Stop replication on secondary server :(3309, 3310)
Run the following command on MySQL:
stop slave;
reset slave;
4, check binary log file and Position value on primary server :(3307, 3308)
Run the following command on MySQL:
Run the show master status command on 3307 and 3308 respectively.
5. Set the Master on the Slave server.
Set secondary servers 3308 and 3309 to 3307, that is, perform the following operations on 3308 and 3309:
Change master to master_host='192.168.199.128', master_user='copy', master_password='123456', master_port=3307, master_log_file='mysql-bin.000001', master_log_pos=154;Copy the codeSet secondary server 3307, 3310, their primary is 3308, that is, on 3307 and 3310 do the following:
Change master to master_host='192.168.199.128', master_user='copy', master_password='123456', master_port=3308, master_log_file='mysql-bin.000001', master_log_pos=154;Copy the code6, run the following command on four MySQL servers: start slave; (MySQL command line)
After this command is executed, the system enters the primary/secondary replication state
- MySQL Master-slave Replication
- Also welcome everybody exchange discussion, if this article has incorrect place, hope everybody many forgive.
- Your support is my biggest motivation, if you help out, give me a thumbs up