MySQL master-Slave synchronization configuration under Linux

Centos6.5 MySQL master-slave sync MySQL version 5.6.25

Primary server: centos6.5 ip:192.168.1.101

From server: centos6.5 ip:192.168.1.102

First, the primary server-related configuration

1. Create a sync account and specify the server address

[Email protected] ~]mysql-uroot-pmysql>use mysqlmysql>grant replication Slave on * * to ' testuser ' @ ' 192.168.1.10 2 ' identified by ' 12345678 '; Mysql>flush privileges #刷新权限

Authorized user testuser can only access the database of the primary server 192.168.1.101 from the 192.168.1.102 address and only have database backup permissions

2. Modify the/ETC/MY.CNF configuration file vi/etc/my.cnf

Add the following parameter under [Mysqld], if the file already exists, do not add server-id=1  log-bin=mysql-bin  #启动MySQL二进制日志系统, BINLOG-DO-DB=OURNEEDDB  #需要同步的数据库binlog-ignore-db=mysql   #不同步mysql系统数据库, if there are other do not want to sync, continue to add [[email protected] ~]/etc/init.d/mysqld Restart #重启服务

3, check the master server Master status (note the file and position items, from the server need these two parameters)

Mysql> Show Master status;+------------------+----------+--------------+------------------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000012 |      120 | ourneeddb| MySQL            |+------------------+----------+--------------+------------------+

4. Export the database

Lock the database before you export the database

Flush tables with read lock; #数据库只读锁定命令 to prevent data from being written when the database is exported

Unlock tables; #解除锁定

Export database structure and data: Mysqldump-uroot-p ourneeddb >/home/ourneeddb.sql

Export stored procedures and functions: Mysqldump-uroot-p-ntd-r ourneeddb > Ourneeddb_func.sql

TIPS:-NTD Export stored procedure,-R export function

Second, from the server-related configuration

1. Modify the/ETC/MY.CNF configuration file vi/etc/my.cnf

[Mysqld] Under Add the following parameters, if the file already exists, do not add server-id=2   #设置从服务器id, must be different from the primary server Log-bin=mysql-bin  # Start the MYSQ binary log system replicate-do-db=ourneeddb   #需要同步的数据库名replicate-ignore-db=mysql   #不同步mysql系统数据库 [email protected]~]/etc/init.d/mysqld Restart #重启服务

2. Import the database

The import process does not elaborate here.

3. Configure Master-Slave synchronization

[Email protected]~]mysql-uroot-pmysql>use MySQL mysql>stop slave;mysql>change master to       master_host= ' 192.168.1.101 ',      master_user= ' testuser ',      master_password= ' 12345678 ',      master_log_file= ' mysql-bin.000012 ',      master_log_pos=120;  #log_file与log_pos是主服务器master状态下的File与Positionmysql >start slave;mysql>show slave status\g;

1. Row ***************************
Slave_io_state:waiting for Master to send event
Master_host:192.168.1.101
Master_user:testuser
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000012
read_master_log_pos:120
relay_log_file:orange-2-relay-bin.000003
relay_log_pos:283
relay_master_log_file:mysql-bin.000012
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:orange
Replicate_ignore_db:mysql,test,information_schema,performance_schema
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:120
relay_log_space:1320
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
Master_server_id:1
Master_uuid:773d2987-6821-11e6-b9e0-00163f0004f9
Master_info_file:/home/mysql/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0

Note See slave_io_running:yes slave_sql_running:yes These two must be Yes and log_file, Log_pos to be in the master state of File , Position the same

If all is correct, then the configuration is successful!

MySQL master-Slave synchronization configuration under Linux