Categories
Uncategorized

Mysql master-slave synchronization configuration scheme (Centos7)

In a recent project to make high availability, need to use data synchronization. Since only two-node, and using the primary synchronization master synchronization cycle may be at risk, it is considering using Mysql primary (Synchronous Master-Slave) from the sync.

You may not come into contact with Mysql data synchronization, data synchronization may find it difficult, but after you follow the following operations again, you will find the original synchronized so simple, after all, we only need to be configured, troubleshoot the underlying concrete realization team as early as Mysql help Achieved. Ado, straight into the topic:

(1) First, to ensure that two devices (master, slave), respectively mounted on both devices Mysql database. After the installation is complete,

Use mysql -u [database username] -p [user password], if able to log mysql installation was successful.

(2) edit the mysql configuration file, the path is generally /etc/mysql/mysql.conf.d/mysqld.cnf (specified by the user during installation Mysql directory,

Such as: /etc/my.conf etc.). Respectively in the main claim, add the following from the [mysqld] mysql module of:

   [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 main fill, fill from 2]

  auto-increment-increment=2

  auto-increment-offset=1

  open_files_limit=65535

  mac_connections=1000

  slave-skip-errors=all

(3) shell connecting primary and secondary servers, mysql -u [database username] -p [User Password], respectively, log in to the master, the slave node mysql, the repl user creates a synchronization, run the following command:

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

Then exit to exit the operation Mysql page, restart the Mysql service command as follows:

  systemctl restart mysql.service

Then enter the main node Mysql, Mysql view the File column and Positon column. Execute the command: show master status; appear the following results

 

 

 

   

(4) Log on to the server, mysql -u [database username] -p [user password] to enter Mysql, 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, Reference File column (3)]’, master_log_pos = [245 , reference (3) in the column Postion];

  exit;

Mysql execution systemctl restart mysql.service restart the service.

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

  start slave;

Execution: show slave status \ G; view the backup service status. If the following two states to Yes, said master-slave synchronization success.

       

 

Next, we can try to insert data into a master database, see the article is enough to automatically increase the data from the database, is not it a little excited about it? Quickly test under the bar.

PS: If there is not synchronization problems?

(1) See the diagram above Master_Log_File property, whether Read_Master_Log_Pos attribute query results consistent with the master node.

(2) may be connected to the database from the master database timeout, go bigger connected delay change it.

(3) If the above can not be resolved, please view the mysql log for the cause.

Leave a Reply