Specific steps to configure non-interactive master-slave synchronization of mysql database

The following mainly brings you the concrete steps of configuring mysql database master-slave synchronization and non-interactive. I hope that the specific steps of configuring mysql database master-slave synchronization non-interactive can bring you practical use. this paper takes a mysql database multi-instance 3306 and 3308 as an example, 3306 as the master database and 3308 as the slave database (multiple single instance configuration is the same as a multi-instance configuration).

I. my.cnf file configuration

1. Modify the my.cnf configuration file. The log-bin,server-id cannot be opened for the main database 3306.

[root@mysql ~] # egrep "log-bin | server-id" / data/ {3306pr 3308} / my.cnf

/ data/3306/my.cnf:log-bin = / data/3306/mysql-bin

/ data/3306/my.cnf:server-id = 1

/ data/3308/my.cnf:#log-bin = / data/3308/mysql-bin

/ data/3308/my.cnf:server-id = 3

two。 Restart databases 3306 and 3308

[root@mysql ~] # / data/3306/mysqlstop

[root@mysql ~] # / data/3306/mysql start

[root@mysql ~] # / data/3308/mysqlstop

[root@mysql ~] # / data/3308/mysqlstart

3. Enter the main database 3306 and query whether log_bin is open and what the server_id is.

[root@mysql] # mysql-uroot-p123456-S / data/3306/mysql.sock

Mysql > show variables like "log_bin"

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | ON |

+-+ +

1 row in set (0.01 sec)

Mysql > show variables like "server_id"

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 1 | |

+-+ +

1 row in set (0.00 sec)

4. Create a user dedicated to synchronizing the database

Mysql > grant replicationslave on *. * to rep@'10.0.0.%' identified by '123456'

# *. * represents all libraries and all tables

Mysql > flush privileges

Mysql > select user,hostmysql.user

Mysql > show grants forrep@'10.0.0.%'

two。 Master library 3306 backup data, import slave library 3308

1. Back up data on the main library 3306

[root@mysql] # mysqldump-uroot-p123456-S / data/3306/mysql.sock-A-- events-B-x-- master-data=1 | gzip > / opt/$ (date +% F) .sql.gz

-A: all libraries-x: global locking table

two。 Import data from library 3308

[root@mysql ~] # ls / opt

2017-07-03.sql.gz

[root@mysql ~] # cd / opt

[root@mysql opt] # gzip-d2017-07-03.sql.gz

[root@mysql opt] # ls

2017-07-03.sql

[root@mysql opt] # mysql-uroot-p123456-S / data/3308/mysql.sock