MySQL主从复制配置

郑全

环境：

 

主库：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服务器上做主从之前已经有数据了，我们就必须要把数据库导出来  然后导入从数据库 （方法多种）

[此贴子已经被作者于2015-04-27 19:14:35编辑过]

郑全

同步验证：

主库操作：

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即可