用mysqldump --master-data 建立slave
来源:互联网 发布:卡盟刷会员永久软件 编辑:程序博客网 时间:2024/06/05 08:37
用mysqldump --master-data 建立slave
先来看看官方文档的解释
Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating.
If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement takes effect when the dump file is reloaded. If the option value is not specified, the default value is 1.
大概就这么个意思:这个参数在建立slave数据库的时候会用到,当这个参数的值为1的时候,mysqldump出来的文件就会包括CHANGE MASTER TO这个语句,CHANGE MASTER TO后面紧接着就是file和position的记录,file和position记录的位置就是slave从master端复制文件的起始位置。默认情况下这个值是1
当这个值是2的时候,chang master to也是会写到dump文件里面去的,但是不会有上面那个作用了(thus is information only)
翻译过来真感觉拗口,呵呵,凑活看看吧。上实例!
一、先dump一个库
mysqldump -uroot -p123456 --master-data=1 --quick --all-databases --flush-logs --lock-all-tables > alldb.sql
二、观察file和position的值,此时的table是被lock住不能写入的
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
| 1 | rep | node2:2514 | NULL | Binlog Dump | 757 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 3 | root | localhost | test | Query | 0 | NULL | show processlist |
| 4 | root | localhost | orders | Query | 23 | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ * FROM `order_status_track` |
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
| 1 | rep | node2:2514 | NULL | Binlog Dump | 757 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 3 | root | localhost | test | Query | 0 | NULL | show processlist |
| 4 | root | localhost | orders | Query | 23 | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ * FROM `order_status_track` |
+----+------+------------+--------+-------------+------+----------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
三、
[root@node1 ~]# grep -i "CHANGE MASTER TO" alldb.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=106;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=106;
四、编辑slave端配置文件如下
log-bin=mysql-bin
server-id = 3
master-host=192.168.1.201
master-port=3306
master-user=rep
master-password=rep
master-connect-retry=60
server-id = 3
master-host=192.168.1.201
master-port=3306
master-user=rep
master-password=rep
master-connect-retry=60
五、重启slave端mysql
[root@node1 mysql]# /etc/init.d/mysql stop
Shutting down MySQL...... [ OK ]
[root@node1 mysql]# /etc/init.d/mysql start
Starting MySQL... [ OK ]
Shutting down MySQL...... [ OK ]
[root@node1 mysql]# /etc/init.d/mysql start
Starting MySQL... [ OK ]
六、此时netstat -an|grep 3306 看到slave和master的连接已经建立。现在不需要它们之间连接,登陆slave端,执行stop slave,连接成功断开
七、Mysqldump 导入master 导出的文件dumpfile
mysql -uroot -p123456 < alldb.sql
八、开启slave端同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
在这一步骤,如果--master-data 参数为二,此时你会发现你还需要输入
CHANGE MASTER TO
MASTER_HOST='192.168.1.202',
MASTER_USER='rep',
MASTER_PASSWORD='rep';
MASTER_HOST='192.168.1.202',
MASTER_USER='rep',
MASTER_PASSWORD='rep';
类似的参数后,才能继续执行start slave。当然这里你也可以写到配置文件中去
九、查看slave端状态,大功告成
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 106
Relay_Log_File: node1-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000019
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 406
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 106
Relay_Log_File: node1-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000019
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 406
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
参考 http://asmboy001.blog.**.com/340398/197750
转自 http://blog.itpub.net/758322/viewspace-680471
0 0
- 用mysqldump --master-data 建立slave
- mysql slave创建:mysqldump参数--master-data
- mysqldump --master-data建立主从服务
- mysqldump --master-data
- mysqldump --master-data
- mysqldump详解之--master-data
- mysqldump的--master-data参数
- mysqldump关于master-data参数
- mysqldump参数之--master-data
- mysqldump的--master-data参数
- MYSQLdump参数详解 --master-data
- mysqldump --master-data=2 --single-transaction
- mysqldump的single-transaction和master-data
- Mysqldump 详解之 --master-data --single-transaction
- master/slave主从数据库手动同步之master-data
- master-slave
- mysql5.5 建立主从复制 ( set up master-slave replication )
- Redis在一台主机上建立Master-Slave
- LINUX 下 tomcat的安装和配置
- Dota2自定义地图模板简介
- LintCode--best-time-to-buy-and-sell-stock(买卖股票的最佳时机)
- 国内外自然语言处理(NLP)研究组
- HDU 5452 Minimum Cut(LCA & RMQ & DFS)——2015 ACM/ICPC Asia Regional Shenyang Online
- 用mysqldump --master-data 建立slave
- css知多少(8)——float上篇
- 关于安卓HTTP请求用HttpUrlConnection还是HttpClient好
- 初学者如何查阅自然语言处理(NLP)领域学术资料
- HDU 5461 Largest Point (水)
- Linux下mysql的基本操作
- Java异步事件:轮询与中断
- 保持激情
- 软件测试分类