环境：
master server: 10.224.194.239
slave server: 10.224.194.237
步骤：
1.分别在master/slaver mysql db 创建backup user：
grant file on *.* to backup@10.224.194.239 identified by 'pass';
grant replication slave on *.* to backup@10.224.194.239 identified by 'pass';
grant file on *.* to backup@10.224.194.237 identified by 'pass';
grant replication slave on *.* to backup@10.224.194.237 identified by 'pass';
2.在master server配置/etc/my.cf 文件：
server-id = 1
binlog-do-db=test
binlog-ignore-db = mysql
replicate-do-db=test
replicate-ignore-db = mysql
master-host=10.224.194.237
master-user=backup
master-password=pass
master-port=3306
master-connect-retry=60
slave-skip-errors=all
3.在master server配置/etc/my.cf 文件：
server-id = 2
binlog-do-db=test
binlog-ignore-db = mysql
replicate-do-db=test
replicate-ignore-db = mysql
master-host=10.224.194.239
master-user=backup
master-password=pass
master-port=3306
master-connect-retry=60
slave-skip-errors=all
4.重启mysql数据库，验证命令如下：
查看master状态
show master status;
mysql> show master status;
+-----------------+----------+--------------+------------------+
| file | position | binlog_do_db | binlog_ignore_db |
+-----------------+----------+--------------+------------------+
| mysqllog.000003 | 301 | test | mysql |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)
查看slave状态
show slave status /g;
mysql> show slave status /g;
*************************** 1. row ***************************
slave_io_state: reconnecting after a failed master event read
master_host: 10.224.194.239
master_user: backup
master_port: 3306
connect_retry: 60
master_log_file: mysqllog.000003
read_master_log_pos: 301
relay_log_file: mysqlgsb-relay-bin.000082
relay_log_pos: 348
relay_master_log_file: mysqllog.000003
slave_io_running: no
slave_sql_running: yes
replicate_do_db: test
replicate_ignore_db: mysql
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: 301
relay_log_space: 650
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: null
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
1 row in set (0.00 sec)
error:
no query specified
查看同步进程：
mysql> show processlist /g;
*************************** 1. row ***************************
id: 1
user: system user
host:
db: null
command: connect
time: 4186
state: waiting for master to send event
info: null
*************************** 2. row ***************************
id: 2
user: system user
host:
db: null
command: connect
time: 3745
state: has read all relay log; waiting for the slave i/o thread to update it
info: null
*************************** 3. row ***************************
id: 5
user: root
host: mysqlpri.webex.com:28293
db: null
command: query
time: 0
state: null
info: show processlist
*************************** 4. row ***************************
id: 6
user: backup
host: 10.224.194.237:41729
db: null
command: binlog dump
time: 135
state: has sent all binlog to slave; waiting for binlog to be updated
info: null
4 rows in set (0.00 sec)
error:
no query specified
本文出自 “迈小步、不停步！” 博客
bitscn.com