MySQL multi-master and one-slave data backup method tutorial

Overview

Operations on any one database are automatically applied to the other database, always keeping the data in the two databases consistent.

This has the following benefits:

• It can be used for disaster recovery, and if one of them fails, you can switch to another one.
• Load balancing can be done, and requests can be distributed to any of the machines to improve website throughput. For off-site hot backup, it is especially suitable for disaster recovery.

MySQL master-slave replication method

1 When the Master database operates any database, the operation record will be written to the biglog log file

2 The slave database obtains the log file of the master database through the network, writes it to the local log system, and then completes the database events in the database one by one

3 The Slave redoes the events in the relay log and reflects the changes on the Master in its own database, so the data on both ends is exactly the same.

environment

Operating system: CentOS

MySQL version: mysql-5.6.26 (the database versions of the master and slave must be consistent)

Master1 Configuration

1 Enable binlog logging

vim /etc/my.cnf

server-id=6
log-bin=mysql-bin

2 Restart MySQL, log in and authorize

mysql -uroot -p123456

grant replication slave, replication client on *.* to 'repl'@'10.211.55.7' identified by '123456';

The ip address is the ip address of the slave server

3 View log status

show master status;

Master2 Configuration

1 Enable binlog logging

vim /etc/my.cnf

server-id=8
log-bin=mysql-bin

2 Restart MySQL, log in and authorize

mysql -uroot -p123456

grant replication slave, replication client on *.* to 'repl'@'10.211.55.7' identified by '123456';

The ip address is the ip address of the slave server

3 View log status

show master status;

Slave Configuration

1 Modify the configuration file (note that the port where the slave's default database is started must be closed service mysql stop)

vim /etc/my.cnf

[mysqld]
 binlog-ignore-db=mysql
 binlog_format=mixed
 expire_logs_days=7
 slave_skip_errors=1062
 relay_log=mysql-relay-bin
 log_slave_updates=1

[mysqld_muliti]
 mysqld=/usr/bin/mysqld_safe
 mysqladmin=/usr/bin/mysqladmin
 user=root
 password=123456
[mysqld6]
 port=3306
 datadir=/home/mysql/data6
 pid-file=/home/mysql/data6/mysql.pid
 socket=/home/mysql/data6/mysql.sock
 user=mysql
 server-id=7
[mysqld8]
 port=3307
 datadir=/home/mysql/data8
 pid-file=/home/mysql/data8/mysql.pid
 socket=/home/mysql/data8/mysql.sock
 user=mysql
 server-id=7

2 Initialize the build directory

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/data6 &
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/data8 &

3 Modify directory permissions

chown -R mysql /home/mysql/data6

chown -R mysql /home/mysql/data8

4 Start the service

mysqld_multi --defaults-file=/etc/my.cnf start 6

mysqld_multi --defaults-file=/etc/my.cnf start 8

5 Login test (and authorize separately)

mysql -P 3306 -S /home/mysql/data6/mysql.sock

mysql> change master to master_host='10.211.55.6', master_user='repl', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=120;
mysql> start slave;

mysql -P 3307 -S /home/mysql/data8/mysql.sock

mysql> change master to master_host='10.211.55.8', master_user='repl', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=120;
mysql> start slave;

ok, that's it.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.