MySQL AB replication MS replication

AB replication: The master server builds a synchronization environment by modifying the MySQL configuration file, enabling the server to synchronize the primary server's database.

Note: Both the master and slave servers need to shut down the firewall.

Primary server:

Modify MySQL configuration file to generate log permanently

#vim/etc/my.cnf

Log-bin= ' Masterlog '

Server-id=1

To create an account:

Mysql>grant Replication Slave,reload,super On * * to 'slave' @ 'percent' identified by '123 ';

Mysql>flushreplication slave;

Replication slave:

With this permission, you can view the binary logs from the server and read from the primary server.
Super Privilege:
Allows the user to use the SET statement that modifies the global variable and the change master statement
Reload Permissions:
You must have reload permission to perform flush [tables | logs | privileges] privileges;

Slave: User name, can define itself

%: Working environment, generally write from the server's IP, or do a local resolution after the direct write domain name.

#systemctl Restart Mysqld

From the server:

To modify the MySQL configuration file:

#vim/etc/my.cnf

[Mysqld]

server-id=2

Log-bin=slavelog//write not read the requirements (double master must write, use Gtid copy must write)

#systemctl Restart Mysqld

Specify primary server information

Mysql>help Change Master to
Mysql>change MASTER to
Master_host= ' master2.example.com ',//home Server IP
Master_user= ' replication ',//master server user
Master_password= ' PASSWORD ',//user password
master_port=3306,//Port
Master_log_file= ' master2-bin.001 ',//binlog log file name
master_log_pos=4,//log location
master_connect_retry=10; If the number of attempts to synchronize failed

Mysql>start slave;

Mysql>show slave Status\g

After the synchronization is complete, test, if not successful, delete the log from Binlog, restart the service

However, this method needs to specify the log name of the main configuration file, the log location, and more cumbersome.

You can use the Gtid method to automatically find the sync point:

The Gtid feature needs to be turned on in the master/slave server configuration file:

The active configuration needs to be added:

#vim/etc/my.cnf

Gtid_mode=on

Enforce_gtid_consistency=1

Only the following configuration is required from the server:

Mysql>change MASTER to
Master_host= ' master2.example.com ',//home Server IP
Master_user= ' replication ',//master server user
Master_password= ' PASSWORD ',//user password
master_port=3306,//Port
Master_auto_position=1

After completion, test, if not synchronized, remove the Binlog from the server and restart the service.

MySQL AB replication MS replication