主服务器:centos 5.2 mysql 5.1.35 源码 IP:192.168.1.22
从服务器:centos 5.2 mysql 5.1.35 源码 IP:192.168.1.33
配置:
一、主服务器
1.1、创建一个复制用户,具有replication slave 权限。
mysql>grant replication slave on *.* to 'repl'@'192.168.1.22' identified by 'repl';
1.2、编辑my.cnf文件
vi /etc/my.cnf 添加 server-id=1 并开启log-bin二进制日志文件 log-bin=mysql-bin
注:需要把默认的server-id=1去掉
1.3、启动mysql数据库
mysqld_safe --user=mysql &
1.4、设置读锁
mysql>flush tables with read lock;
1.5、得到binlog日志文件名和偏移量
mysql>show master status;
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.0000010 | 106| | | +------------------+----------+--------------+------------------+
1.6、备份要同步的数据库
mysqldump test > test.sql
1.7、解锁
mysql>unlock tables;
二、从服务器
2.1、编辑my.cnf文件
vi /etc/my.cnf 添加 server-id=2
注:需要把默认的server-id=1去掉
2.2、启动从数据库
mysqld_safe --user=mysql &
2.3、对从数据库进行相应设置
mysql> change master to -> master_host='192.168.1.22' -> master_user='repl' -> master_password='repl' -> master_log_file='mysql-bin.0000010' -> master_log_pos=106;
2.4、启动从服务器slave线程
mysql>start slave;
执行show processlist命令显示以下进程:
mysql>show processlist\G *************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 2579 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL表示slave已经连接上master,开始接受并执行日志
2.5、查看slave线程状态
mysql>show slave status;
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.22
Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.0000010 Read_Master_Log_Pos: 106 Relay_Log_File: centos-relay-bin.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.0000010 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: 830 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)
验证是否配置正确
在从服务器上执行
show slave status\G; Waiting for master to send event Slave_IO_Running: Yes Slave_SQL_Running: Yes
如以上二行同时为Yes 说明配置成功
测试
1、在主服务器test数据库中创建user表
mysql>use test; mysql>create table user(id int);
2、在从服务器中查看user表
mysql>use test; mysql> show tables like 'user'; +----------------------+ | Tables_in_test(user) | +----------------------+ | user | +----------------------+ 1 row in set (0.00 sec)
说明主从数据同步成功。
问题?
1.在从数据库中查看slave状态时出现
The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)
说明方从服务器里my.cnf中的server-id有相同的。
解决办法:
修改my.cnf里的server-id,并重启数据库服务。my.cnf文件默认有server-id=1
其它说明
主服务器my.cnf
#binlog-do-db=需要备份的数据库名,可写多行 #binlog-ignore-db=不需要备份的数据库名,可写多行 从服务器my.cnf
# replicate-do-db=test 需要备份的数据库名 # replicate-ignore-db=mysql 忽略的数据库 # master-connect-retry=60 如果从服务器发现主服务器断掉,重新连接的时间差(秒)
以下设置也可直接修改my.cnf配置文件
log-bin=mysql-bin master-host=192.168.1.22
master-user=repl
master-password=repl master-port=3306
主从服务器同步维护
由于各种原因,导致主从数据不一致,在负载低的时候,进行手动同步.
在主服务器上执行
mysql>flush tables with read lock;
Query OK,rows affected (0.01 sec)
mysql>show master status;
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.0000011 | 260| | | +------------------+----------+--------------+------------------+
在从服务器上执行
先得到当前主服务器的二进制文件名和偏移量,执行命令使从服务器与主服务器同步
mysql>select master_pos_wait('mysql-bin.0000011','260'); +--------------------------------------------------+ | master_pos_wait('mysql-bin.0000011','260') | +--------------------------------------------------+ | 0 | +--------------------------------------------------+ 1 row in set (0.01 sec)
同步完成后,在主服务器上执行解锁
mysql>unlock tables;
切换主从服务器
当主服务器出现故障时,可将从服务器当主服务器来使用.步骤如下:
1、保证所有从数据库都已经执行了relay log中的全部更新,在从服务器中执行
stop slave io_thread,用show processlist检查,查看状态是否是Has read all relay log,表示更新完成.
mysql>stop slave io_thread; Query OK,0 affected (0.00 sec) mysql>show processlist\G;
*************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 4757 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL
2、在从服务器上执行stop slave,reset master命令,重置成主数据库
mysql>stop slave; Query OK,0 affected (0.00 sec) mysql>reset master; Query OK,0 affected (0.00 sec)
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn