mysql主从同步

主

在my.cnf中添加以下配置，binlog文件保存的位置，忽略用户表的同步
vim /etc/my.cnf
server-id = 1
log-bin=/opt/mysqldb/mysql-bin
replicate-ignore-table = mysql.user

添加同步所需的账号密码
grant replication slave on *.* to tongbu@'%' identified by '123456';
fulsh privileges;

同步前先锁表，防止数据写入
flush table with read lock;

查看此时的数据节点，记录下来，开始在此节点进行同步
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1407 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

打开新窗口备份数据库
mysqldump -u root -p -B -A > all.sql

解锁数据库
show master status;
unlock table;

从

在my.cnf中添加以下配置，binlog文件保存的位置，忽略用户表的同步
vim /etc/my.cnf
server-id = 2
log-bin=/opt/mysqldb/mysql-bin
replicate-ignore-table = mysql.user

导入节点前的所有数据
mysql -u root -p < all.sql

开始同步，同步的主服务器ip，端口，账号，密码，binlog文件名，节点位置
change master to master_host = '192.168.1.171',master_port = 3306,master_user = 'tongbu',master_password = '123456',master_log_file = 'mysql-bin.000003',master_log_pos = 1407;

打开同步
start slave;

查看同步状态
show slave status\G