采用全库复制的方案,方便管理mysql 5.5 + rhel5.8
10.4.11.12 master
10.4.11.13 slave
---master端设置
1, 创建复制帐号,需要复制和slave权限
mysql> grant replication slave, replication client on *.* to repl@'10.4.11.12 identified by 'mysql;
query ok, 0 rows affected (0.00 sec)
2, 修改参数,并重启生效
#skip-networking
server-id = 12
# uncomment the following if you want to log updates
log-bin=mysql-bin
重启mysql
service mysql restart
---slave端的设置
3,slave库的参数设置,同样需要重启生效
vi /etc/my.cnf
#skip-networking
server-id = 13
# uncomment the following if you want to log updates
log-bin=mysql-relay-bin
4,master导出数据到slave库
如果数据库使用的是myisam表类型的话,可按如下方式操作:
shell> mysqldump --all-databases --master-data=1 > data.sql
如果数据库使用的是innodb表类型的话,则应该使用single-transcation:
shell> mysqldump --all-databases --single-transaction --master-data=1 > data.sql
slave端导入数据
mysql < data.sql
5,查看master的配置
mysql> show master status;
+------------------+----------+--------------+------------------+
| file | position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6,启动复制,slave端运行如下命令
mysql> show slave status\g
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 172.16.251.11
master_user: repl
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000001
read_master_log_pos: 1278
relay_log_file: linux2-relay-bin.000002
relay_log_pos: 503
relay_master_log_file: mysql-bin.000001
slave_io_running: yes
slave_sql_running: yes
replicate_do_db:
……..
8,测试复制情况
master端新建一个表
slave查看是否复制完成
如对本文有疑问, 点击进行留言回复!!
服务器Centos部署MySql并连接Navicat过程详解
网友评论