【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