Mysql 主从同步配置以及问题处理 一:mster端 mysql安装略 1, 为slave同步添加权限。 mysql>grant replication slave on *.* to 'root'@'192.168.40.39' identified by '123456'; 2,修改配置文件 [iyunv@KVM_db01 data]# vi /etc/my.cnf [mysqld] 字段添加 server-id=1 log-bin=log binlog-do-db=db01 #需要同步的数据库 binlog-ignore-db=mysql #忽略同步的数据库 3, 重启mysqld 服务,查看master状态 mysql> show master status\G; *************************** 1. row *************************** File: log.000002 Position: 106 Binlog_Do_DB: db01 Binlog_Ignore_DB: mysql 1 row in set (0.00 sec) 二:slave 端, 1,修改mysql配置文件 [iyunv@KVM_db02 ~]# vi /etc/my.cnf [mysqld] server-id=2 master-host=192.168.40.38 master-user=root master-password=123456 master-port=3306 master-connect-retry=30 replicate-do-db=db01 三:同步数据库 同步完成后重启master端mysql,然后重启slave端。 四:查看slave端状态 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.40.38 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log.000002 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 245 Relay_Master_Log_File: log.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: dedecmsv57utf8sp1 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: 401 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) 查看IO和SQL 都为YES 说明同步配置成功。 Slave_IO_Running: Yes Slave_SQL_Running: Yes 五:测试主从服务器是否能同步
在主服务器上面新建一个表,必须在db01数据下
mysql> use db01
Database changed
mysql> create table test(id int,name char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(1,'test');
Query OK, 1 row affected (0.00 sec) 然后去从服务器上查看是否同步。 问题处理: 1,ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO, 先关闭slave slave stop; 再执行: mysql>change master to master_host='192.168.1.222', master_user='root', master_password='123456', master_log_file='log.000002' , master_log_pos=106; mysql> slave start; Slave_SQL_Running: No 原因:
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后,事务回滚造成的. 首先停掉Slave服务:slave stop
到主服务器上查看主机状态:
记录File和Position对应的值。
到slave服务器上执行手动同步: mysql> change master to
> master_host='192.168.40.38',
> master_user='root',
> master_password='123456',
> master_port=3306,
> master_log_file='log.00002',
> master_log_pos=106;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec) 办法2: mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
|