• 1
  • 2
  • 3
  • 4
  • 5
mysql数据库问题 首 页  »  帮助中心  »  数据库  »  mysql数据库问题
MYSQL:主从复制
发布日期:2016-4-24 21:4:8

  一、

  主: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