Mysql master-slave synchronization configuration scheme under Centos7 system

Preface

Recently, when working on a high-availability project, data synchronization is required. Since there are only two nodes and the master-master synchronization may have the risk of circular synchronization, we consider using MySQL master-slave synchronization.

If you have never been exposed to MySQL data synchronization, you may think that data synchronization is difficult, but after you follow the following operations, you will find that synchronization is so simple. After all, we only need to know how to configure and troubleshoot problems, and the underlying specific implementation of MySQL team has already helped to achieve it.

Without further ado, let’s get straight to the point:

(1) First, ensure that there are two devices (master and slave) and install the MySQL database on each device. After the installation is complete,

Use mysql -u [database username] -p [user password]. If you can log in to MySQL, the installation is successful.

(2) Edit the MySQL configuration file. The path is generally /etc/mysql/mysql.conf.d/mysqld.cnf (the directory specified by the user when installing MySQL.

Such as: /etc/my.conf, etc.). Add the following content under the [mysqld] module in the master and slave mysql as required:

  [mysqld]

  default-storage-engine=innodb

  innodb_file_per_table=1

  collation-server=uft8_general_ci

  init-connect='SET NAMES uft8'

  bind-address=0.0.0.0

  log_bin=mysql-bin

  relay_log=mysql-relay-bin

  expire_logs_days=100

  skip-slave-start=1

  binlog_do_db=[name of the database to be synchronized]

  binlog-ignore-db=mysql,sys,information_schema,performance_schema

  server-id=[1 for master, 2 for slave]

  auto-increment-increment=2

  auto-increment-offset=1

  open_files_limit=65535

  mac_connections=1000

  slave-skip-errors=all

(3) Use shell to connect to the master and slave servers, mysql -u [database user name] -p [user password], log in to the master and slave node mysql respectively, create a synchronization user repl, and execute the following command:

  grant all privileges on *.* to 'repl'@'%' identified by 'repl' with grant option;

Then exit the MySQL operation page and restart the MySQL service. The command is as follows:

  systemctl restart mysql.service

Then enter the main node Mysql and view the File column and Positon column of Mysql. Execute the command: show master status; the result is as follows

(4) Log in to the slave server, enter MySQL using mysql -u [database username] -p [user password], and execute:

  stop slave;

  reset slave;

  change master to master_host='[master node IP]',master_user='repl',master_password='repl',master_log_file='[mysql-bin.000001, refer to the File column in (3)]',master_log_pos=[245, refer to the Position column in (3)];

  exit;

Run systemctl restart mysql.service to restart the MySQL service.

(5) Start the slave server slave library, mysql -u [database username] -p [user password] to enter the slave node Mysql, and execute the following command:

  start slave;

Execute: show slave status\G; to view the backup service status. If the following two states are Yes, it means that the master-slave synchronization is successful.

Next, you can try to insert a piece of data into the main database to see if the data can be automatically added to the slave database. Isn’t it a little exciting? Let’s test it now.

PS: What if there is a synchronization problem?

(1) Check whether the Master_Log_File attribute and Read_Master_Log_Pos attribute in the above figure are consistent with the query results of the master node.

(2) The connection from the database to the main database may have timed out. Increase the connection delay.

(3) If the above problem cannot be solved, please check the MySQL log to find the cause.

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. Thank you for your support of 123WORDPRESS.COM.