MySQL 主从复制搭建
环境:
master ip: 192.168.10.10
salve ip: 192.168.10.20
1)在两台机器上面安装好mysql
2)修改master my.cnf 添加如下几行
server-id = 1
log-bin = /opt/mysql/log/mysql-bin.log
datadir = /opt/mysql/data
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
3)修改slave my.cnf 添加如下行
server-id=2
relay-log-index=salve-relay-bin.index
relay-log=slave-relay-bin
datadir=/opt/mysql/data
4)重启master 和 slave MySQL服务
service mysqld restart
5)在master上创建复制账号
mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY ‘mysql‘;
6)查看master 状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 739 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
7)如果需要同步原有的库,还需要做如下操作,否者调到第11步
mysql> FLUSH TABLES WITH READ LOCK;
在主机上执行FLUSH TABLES WITH READ LOCK语句来刷新所有的表并阻断写操作
8) 使用mysqldump命令备份需要同步的数据库
[root@localhost ~]$ mysqldump -u root dbname > /home/root/dbname.sql
9)在salve上恢复数据
先将备份的sql文件复制到salve
[root@localhost ~]$ mysql -u root –e ‘create database dbname;‘
[root@localhost ~]$ mysql -u root dbname < /home/root/dbname.sql
10) salve恢复完成后重新打开master的读写功能
mysql> UNLOCK TABLES;
11)在slave上执行如下命令开始同步数据
mysql> change master to master_host=‘192.168.10.10‘,
master_user=‘slave‘,
master_password=‘mysql‘,
master_log_file=‘mysql-bin.000004‘,
master_log_pos=1790;
mysql> start slave
12) 最后在master上创建表验证
本文出自 “专注于Oracle性能调优” 博客,转载请与作者联系!
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。