主服务器的IP:10.101.1.1
从服务器的IP:10.101.1.2
MySQL Replication的基本原理是,将主库上的写操作(INSERT/UPDATE/DELETE)记录到binlog中,由replication账号将SQL语句同步到从库执行,以完成复制/同步操作。
因此,MySQL Replication不会同步主库已存在但未记录操作的数据,在开始同步前保持主从数据库结构的一致,否则同步的SQL可能会执行出错。
(1) 在主服务器上建立同步帐号
10.101.1.1 # mysql –uroot –p{password}
mysql>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.101.1.%'IDENTIFIED BY 'repl123';
mysql>FLUSH PRIVILEGES;
(2) 配置
主服务器配置,server-id=master_id [1到232–1],建议用ip后缀
----- my.cnf --------
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db=mydb
binlog-ignore-db=mysql
-----------------------
其他配置:
log-slave-updates
slave-skip-errors
------------------------
重启主服务器
从服务器配置,server-id=slave_id [1到232–1]
----- my.cnf --------
[mysqld]
server-id=2
replicate-do-db=mydb
------------------------
其他配置:
replicate-do-db=需要复制的DB,多个数据库重复设置即可。
replicate-ignore-db=不需要复制的数据DB,多个数据库重复设置即可。
replicate-do-table
replicate-ignore-table
replicate-wild-do-table
replicate-wild-ignore-table
---------------------------
重启从服务器
(3) 在从库服务上启动Replication
查看主库信息:
mysql> show master status\G
*************************** 1. row***************************
File: mysql-bin.000002
Position: 107
Binlog_Do_DB: db_dev
Binlog_Ignore_DB: mysql
------------------------------------------------
在从库启动复制(也可以在从库的my.cnf中配置,不推荐):
10.101.1.2 # mysql -uroot –p{password}
mysql>CHANGE MASTER TO MASTER_HOST='10.101.1.1',
-> MASTER_USER='replication',
-> MASTER_PASSWORD='repl123',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=107;
----------也可配置my.cnf--------
master-host = 10.101.1.1
master-user = replication
master-password = repl123
master-port = 3306
-------------------------------------------------------------------
mysql>start slave; #启动复制
mysql>SHOW SLAVE STATUS\G #查看状态, G后面不需要’;’
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:Waiting for master to send event
Master_Host:10.101.1.1
Master_User:replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:linux-79vf-bin.000007
Read_Master_Log_Pos: 4769
Relay_Log_File:linux-Server1-relay-bin.000002
Relay_Log_Pos: 4920
Relay_Master_Log_File:linux-79vf-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
------------------------------------------------
查看主库状态:
mysql>show master status\G
mysql>show processlist \G
mysql>show variables like "%log%";
mysql> show processlist \G
*************************** 3. row ***************************
Id: 19
User: replication
Host: 10.101.1.2:40010
db: NULL
Command: Binlog Dump
Time: 1350
State: Master has sent all binlogto slave; waiting for binlog to be updated
Info: NULL
--------------------------------------------------
---------------------------------异常处理1----------------------------------
从服务状态:
Last_IO_Errno:1236
Last_IO_Error:Got fatal error 1236 from master when reading data from binary log: 'Could notfind first log file name in binary log index file'
解决:
mysql> slave stop;
登录主MySQL服务:
mysql> show master status;
+-----------------------+----------+--------------+------------------+
| File | Position| Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+--------------+------------------+
| mysql-bin.000001 | 18525| | |
+-----------------------+----------+--------------+------------------+
mysql> flush logs;
mysql> show master status;
+-----------------------+----------+--------------+------------------+
| File | Position| Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+--------------+------------------+
| mysql-bin.000002 | 107| | |
+-----------------------+----------+--------------+------------------+
回到从MySQL服务:
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=107;
mysql> slave start;
mysql> show slave status\G
如果仍然错误:
到主服务器查看mysqlbinlog:
10.101.1.1 # find / -name mysql-bin*
/var/lib/mysql/ mysql-bin.index
/var/lib/mysql/ mysql-bin.000001
/var/lib/mysql/ mysql-bin.000002
# mysqlbinlog --start-position=107 /var/lib/mysql/ mysql-bin.000002
mysqlbinlog: unknown variable 'default-character-set=utf8'
# mysqlbinlog --no-defaults --start-position=107 /var/lib/mysql/ mysql-bin.000002
切换MASTER_LOG_POS到最近的点(at postion)
---------------------------------------------------------------------------------------------------------------------------
(4) Replication测试
在主库插入一条记录,检查从库,也增加了一条数据。
停掉从MySQL,在主库插入记录,启动从MySQL,检查两边数据一致性。