MySQL master configuration under Linux

First, the primary server-related configuration

1. Create a sync account and specify the server address

[Email protected] ~]mysql-uroot-p
Mysql>use MySQL
Mysql>grant replication Slave on * * to ' testuser ' @ ' 192.168.1.102 ' 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 it already exists in the file, do not add
Server-id=1
Log-bin=mysql-bin #启动MySQL二进制日志系统,
BINLOG-DO-DB=OURNEEDDB #需要同步的数据库
Binlog-ignore-db=mysql #不同步mysql系统数据库, if there are others that you do not want to sync, continue adding
[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 | | 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

Add the following parameter under [Mysqld], if it already exists in the file, do not add
server-id=2 #设置从服务器id, must be different from the primary server
Log-bin=mysql-bin #启动MySQ二进制日志系统
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-p
Mysql>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与Position
Mysql>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 items must be the same for Yes and file,position for Log_file, Log_pos to be in the master state

If all is correct, then the configuration is successful!

MySQL master configuration under Linux