【MySQL】主从复制
lunb2728
9年前
来自: http://my.oschina.net/u/204498/blog/614003
一、
主:hftest0001
从:hftest0002
二、
主:mysql -uroot -p
[root@hftest0001 mysql]# vi /etc/my.cnf [mysqld] ... server-id=201 log-bin=master-bin log-bin-index=master-bin.index ... [mysqld_safe] ... ... [root@hftest0001 mysql]# mysql -uroot -p mysql> create user repl; mysql> grant replication slave on *.* to 'repl'@'${slave_ip}' identified by '${repl_passwd}'; mysql> exit; [root@hftest0001 mysql]# service mysqld restart [root@hftest0001 mysql]# mysql -uroot -p mysql> show master status; mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 1459 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
从:mysql -uroot -p
[root@hftest0002 ~]# vi /etc/my.cnf ... server-id=155 relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin ... [root@hftest0002 mysql]# service mysqld restart [root@hftest0002 mysql]# mysql -uroot -p mysql> change master to master_host='${repl_ip}', //Master 服务器Ip > master_port=3306, > master_user='repl', > master_password='${repl_passwd}', > master_log_file='master-bin.000001',//Master服务器产生的日志 > master_log_pos=1459; mysql> start slave; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.224.246.201 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1459 Relay_Log_File: slave-relay-bin.000005 Relay_Log_Pos: 480 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... ... ...
三、指定DB复制
从:mysql -uroot -p
[root@hftest0002 ~]# vi /etc/my.cnf [mysqld] ... ... replicate-do-db=repl3 ==>指定复制db ... [root@hftest0002 mysql]# service mysqld restart [root@hftest0002 mysql]# mysql -uroot -p mysql> change master to master_host='${repl_ip}', //Master 服务器Ip > master_port=3306, > master_user='repl', > master_password='${repl_passwd}', > master_log_file='master-bin.000001',//Master服务器产生的日志 > master_log_pos=1459; mysql> start slave; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.224.246.201 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1459 Relay_Log_File: slave-relay-bin.000005 Relay_Log_Pos: 480 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: repl3 => 指定的DB