环境：
主库：192.168.0.15
从库：192.168.0.16
一、主服务器上：
注意：mysql参数
binlog-do-db = 数据库名 （你要备份的数据库）
binlog-ignore-db = 数据库名 （你不需要备份的数据库）
如果没有在/etc/my.cnf添加以上2个参数，那就是说明备份所有数据库
(1）确保/etc/my.cnf 中有如下参数，一般情况下都是都有的，但是以确保实验的成功率还是检查一下比较好。假如没有如下参数，自己手动添加，并重启mysql服务器
[mysqld]
log-bin=mysql-bin （启动二进制文件）
server-id=1 （服务器ID）
(2) 登录mysql服务器，添加一个叫做repuser的用户，并授权给从服务器
[root@localhost ~]# mysql -uroot –proot 登录mysql
mysql> grant replication slave on *.* to 'repuser'@'192.168.0.16' identified by '123'; 创建 repuser用户，密码设置为123 并授权给192.168.0.16使用。
(3) 查询主数据库的状态，并记下File和Position的值，这个在配置从数据库的时候用的到
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| mysql-binlog.000029 | 332 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
二、从服务器上
（1）修改配置文件/etc/my.cnf 将service-id=1修改为server-id=2
（2）并重启mysql服务器
[root@localhost ~]# service mysqld stop
[root@localhost ~]# mysqld_safe --user=mysql &
（3）登录mysql服务器
[root@localhost ~]# mysql -uroot –proot
mysql>change master to master_host='192.168.0.15',master_user='repuser',master_password='123',master_log_file='mysql-binlog.000029',master_log_pos=332;
(4) 启动slave同步
mysql > start slave;
（5） 检查主从同步，如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes，则主从复制连接正常。
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.15
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000031
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 286
Relay_Master_Log_File: mysql-binlog.000031
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: 120
Relay_Log_Space: 626
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
Master_UUID: 34cba72c-ea1f-11e4-bedc-000c293e4707
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
OK! 主从复制完成，现在可以去验证了
验证方法：去主服务器上新建一个数据库，然后登录从服务器看看刚才在主服务器新建的数据库在不在
注意：如果master服务器上做主从之前已经有数据了，我们就必须要把数据库导出来 然后导入从数据库 （方法多种）
同步验证：
主库操作：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| innodb_memcache |
| mysql |
| performance_schema |
| sakila |
| sys |
| sztech |
| test |
| world |
+--------------------+
10 rows in set (0.02 sec)
mysql> use sztech
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql>
mysql> show tables;
+------------------+
| Tables_in_sztech |
+------------------+
| dept |
| emp |
| emp_bak |
| emp_bak1 |
| job |
| location |
+------------------+
6 rows in set (0.01 sec)
mysql> select * from emp_bak;
+-------+-----------+--------+------------+
| empid | last_name | salary | hire_date |
+-------+-----------+--------+------------+
| 100 | smitty | 5000 | 2015-04-05 |
| 200 | NULL | NULL | NULL |
| 300 | NULL | NULL | NULL |
+-------+-----------+--------+------------+
3 rows in set (0.00 sec)
查看从库：
mysql> select * from emp_bak;
+-------+-----------+--------+------------+
| empid | last_name | salary | hire_date |
+-------+-----------+--------+------------+
| 100 | smitty | 5000 | 2015-04-05 |
| 200 | NULL | NULL | NULL |
| 300 | NULL | NULL | NULL |
+-------+-----------+--------+------------+
3 rows in set (0.00 sec)
主库操作：
mysql> delete from emp_bak;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from emp_bak;
Empty set (0.00 sec)
从库验证：
mysql> select * from emp_bak;
Empty set (0.00 sec)
确认从库表记录已经删除。
当然，还可以在主库删除表 emp_bak,再在从库去检查表emp_bak是否被删除，以及在主库删除数据库 sztech,看从库是否已经删除数据库sztech.
中间在配置时，报过错，一并发出来，
在备库检查：
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.15
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000029
Read_Master_Log_Pos: 332
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-binlog.000029
Slave_IO_Running: No
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: 1
Exec_Master_Log_Pos: 332
Relay_Log_Space: 120
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: 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).
这个错误，主要是主备库server_id相同了，
检查主备库的 server_id
show variables like 'server_id';
mysql> show variables like 'server_id';
+----------------+--------------------------------------+
| Variable_name | Value |
+----------------+--------------------------------------+
| server_id | 1002 |
+----------------+--------------------------------------+
3 rows in set (0.00 sec)
修改两边的/etc/my.cnf后，问题解决。
在启动slave后，再检查，依然有错：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.15
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000029
Read_Master_Log_Pos: 332
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-binlog.000029
Slave_IO_Running: No
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: 120
Relay_Log_Space: 120
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: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 150427 17:14:38
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
检查，发现，确实是两边的uuid一样了：
(master)root(none)>show variables like '%uuid%'
-> ;
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 34cba72c-ea1f-11e4-bedc-000c293e4707 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
(master)root(none)>
(slave)root(none)>show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 34cba72c-ea1f-11e4-bedc-000c293e4707 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
经检查发现，主库的/usr/local/mysql/data/auto.cnf拷贝到备库去了
[root@sztech1 data]# ls -ltr *.cnf
-rw-r--r-- 1 mysql mysql 3877 4月 24 08:49 server-my.cnf
-rw-r--r-- 1 mysql mysql 12798 4月 24 08:49 server-all.cnf
-rw-rw---- 1 mysql mysql 56 4月 24 09:14 auto.cnf
[root@sztech1 data]# more auto.cnf
[auto]
server-uuid=34cba72c-ea1f-11e4-bedc-000c293e4707
[root@sztech1 data]# pwd
/usr/local/mysql/data
解决办法，按照这个16进制格式，随便改下，重启mysql即可
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |