mysql主从复制
时间：2010-04-06 来源：xhq6632
Master端操作
1、修改配置文件，开启log、修改server-id
[mysqld]
log-bin=mysql-bin
server-id=1
2、为复制建立账号
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@'%' IDENTIFIED BY 'slavepass';
3、执行FLUSH TABLES WITH READ LOCK statement 锁定表，这个终端不要关闭
mysql> FLUSH TABLES WITH READ LOCK;
4、执行SHOW MASTER STATUS 看看master的二进制文件和offset，这两个在后面replication在slave端进行复制需要用到
mysql> SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000014 | 270766137 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
5、备份出master端得数据
shell> mysqldump --all-databases --lock-all-tables >dbdump.db
6、回到第一步的终端执行UNLOCK TABLES解除表锁定
mysql> UNLOCK TABLES;
7、slave端安装一个新的mysql
修改/etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=2
master-host=10.83.200.153
master-port=3306
master-user=repl
master-password=slavepass
master-connect-retry=60
8、重启slave端mysql
执行netstat 看到和master的3306端口之间的连接已经建立
[root@mysqlrep var]# netstat -an|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
tcp 0 0 10.83.201.244:32797 10.83.200.153:3306 ESTABLISHED
9、关闭slave端的连接（以下步骤全部是在slave端完成）
mysql>slave stop;
连接已经没了
[root@mysqlrep var]# netstat -an|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
10、完成master文件的导入
mysql < fulldb.dump
11、完成导入后，进行同步
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.83.200.153',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='slavepass'
-> MASTER_LOG_FILE='mysql-bin.000014',
-> MASTER_LOG_POS=270766137;
12、开始log的复制
mysql> START SLAVE;
13、看看状态 一切OK
mysql> show processlist;
+----+-------------+-----------+------+---------+------------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------------+-----------------------------------------------------------------------+------------------+
| 9 | system user | | NULL | Connect | 5400 | Waiting for master to send event | NULL |
| 10 | system user | | NULL | Connect | 4294965341 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 12 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+------------+-----------------------------------------------------------------------+------------------+
详细参数参考mysql手册
相关阅读 更多 +
