总结下mysql主从复制大致的思路,当然要想做的完美的话,需要注意很多细节。
1 主库,建立从库连接的帐号
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.100.70' IDENTIFIED BY '123456'; flush privileges;
//从数据库可以测试此账户是否可正常使用mysql -h ip -u repl -P端口 -p 2 修改主库的配置文件,开启binlog
log-bin = mysql-bin server-id = 1
3 重启mysql(非必须,待验证),
show master status\G *************************** 1. row *************************** File: mysql-bin.000003 Position: 243 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)
3 停止主库的更新操作,并生成主库的备份 导出前,readlock
flush tables with read lock; mysqldump cswei > /data/cswei.sql
备份完毕,恢复写操作 unlock tables; 4 将备份的数据导入从库 create database cswei;//没有,建立库 source /data/cswei.sql 5 修改从库配置文件 据网上消息介绍,Mysql版本从5.1.7以后开始就不支持“master-host” 类似的参数 在配置文件增加2项,即可 server-id = 2 replicate-do-db = mydatabase 6 从库,赋予从库复制功能
mysql>change master to master_host='192.168.100.70′,master_user='slave', master_password='123456‘,master_log_file='mysql-bin.000001′,master_log_pos=106;
其中master_log_file和master_log_pos通过在主数据库show master status;得到。 端口不是默认端口的话,如master_port=3307,不要加引号。 7 从库 开启复制
mysql> slave start; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.70 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 399 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 545 Relay_Master_Log_File: mysql-bin.000011 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: cswei 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: 399 Relay_Log_Space: 701 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 1
|