MySQL复制之初体验

来源：互联网发布：java dom4j解析xml文件编辑：程序博客网时间：2024/06/06 11:44

配置主库

主库必须启用二进制日志并有唯一的server ID，设置后需要重启mysql服务生效。主从数据传输同步是根据binlog实现的，如果未启用binlog，复制是不可能的。复制组内每个服务器都必须有一个唯一的server ID，此ID用来标识各个服务器，且为1-2的32次方之间的整数,默认为0。

[mysqld]log-bin=master_infoserver-id=1innodb_flush_log_at_trx_commit=1sync_binlog=1

配置从库

从库也必须有唯一的server ID，不能省略，否则拒绝连接。

[mysqld]server-id=2

创建复制用户

帐号需可被从库访问，并具有REPLICATION SLAVE权限，也可不必单独为复制创建帐号。

[master]mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.90.129' IDENTIFIED BY 'mypass';Query OK, 0 rows affected (0.02 sec)

获取主库二进制日志执行位置并同步数据

配置复制需要指定从库开始执行主库的二进制日志的起始执行位置，因此当从库同步主库现有数据时，需要停止在主库上执行更新语句，然后获得主库的当前二进制日志坐标并转储数据。如果主库继续执行更新操作，可能导致主从数据不一致。

获取主库二进制日志坐标的方式：

主库执行FLUSH TABLES WITH READ LOCK 语句:

mysql> FLUSH TABLES WITH READ LOCK;

该语句刷新所有表及块写入语句，对于InnoDB表还会阻塞Commit。非正常退出该session，FLUSH TABLES仍会生效，如使用exit退出锁会被释放。

执行SHOW MASTER STATUS语句确定当前的二进制日志文件名和位置

mysql> show master status;+--------------------+----------+--------------+------------------+-------------------+| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+--------------------+----------+--------------+------------------+-------------------+| master_info.000007 |      120 |              |                  |                   |+--------------------+----------+--------------+------------------+-------------------+1 row in set (0.01 sec)

同步主从数据

如果正在创建一个全新的主从复制可忽略本步骤，如果创建的主库已存在数据，需要创建一个主库的数据快照并在从库中恢复。或许数据快照的方式很多，可使用备份工具如：mysqldump、xtrabackup等，也可直接拷贝主库数据文件。

释放读锁

mysql> UNLOCK TABLES;

从库执行CHANGE MASTER TO语句，启动复制

mysql> change master to master_user='repluser',    -> master_password='mypass',    -> master_host='192.168.90.129',    -> master_log_file='master_info.000007',    -> master_log_pos=120;Query OK, 0 rows affected, 2 warnings (0.11 sec)mysql> start slave;            ## 启动复制线程Query OK, 0 rows affected (0.06 sec)mysql> show slave status \G;*************************** 1. row ***************************               Slave_IO_State:                   Master_Host: 192.168.90.129                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master_info.000004          Read_Master_Log_Pos: 120               Relay_Log_File: relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File: master_info.000004             Slave_IO_Running: No            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB: mysql,information_schema           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: NULLMaster_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: /home/mysql/slave_a/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: 140716 23:49:33     Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 01 row in set (0.00 sec)提示有相同的UUID,查找原因发现是复制文件时将auto.cnf文件也拷贝过来了，该文件里面记录了数据库的uuid，每个库的uuid应该是不一样的。[master]mysql> show variables like '%server%id%';+----------------+--------------------------------------+| Variable_name  | Value                                |+----------------+--------------------------------------+| server_id      | 1                                    || server_id_bits | 32                                   || server_uuid    | 71b00d22-fe27-11e3-bc27-000c29348dbe |+----------------+--------------------------------------+3 rows in set (0.03 sec)[slave]mysql> show variables like '%server%id%';+----------------+--------------------------------------+| Variable_name  | Value                                |+----------------+--------------------------------------+| server_id      | 3                                    || server_id_bits | 32                                   || server_uuid    | 71b00d22-fe27-11e3-bc27-000c29348dbe |+----------------+--------------------------------------+3 rows in set (0.03 sec)

解决办法，按照这个16进制格式，随便改下，重启mysql即可

mysql> show variables like '%server%id%';+----------------+--------------------------------------+| Variable_name  | Value                                |+----------------+--------------------------------------+| server_id      | 3                                    || server_id_bits | 32                                   || server_uuid    | 71b00d22-fe27-11e3-bc27-000c29348888 |+----------------+--------------------------------------+3 rows in set (0.00 sec)mysql> start slave; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show slave status \G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.90.129                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master_info.000004          Read_Master_Log_Pos: 120               Relay_Log_File: relay-bin.000003                Relay_Log_Pos: 285        Relay_Master_Log_File: master_info.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB: mysql,information_schema           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: 452              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: 0Master_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: 71b00d22-fe27-11e3-bc27-000c29348dbe             Master_Info_File: /home/mysql/slave_a/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: 01 row in set (0.00 sec)

验证同步效果

[master]mysql> create table test.sysc (     > id int primary key,    -> name varchar(10)    -> );Query OK, 0 rows affected (0.04 sec)mysql> insert into test.sysc values(1001,'1001');Query OK, 1 row affected (0.00 sec)mysql> insert into test.sysc values(1002,'1002');Query OK, 1 row affected (0.03 sec)[slave]mysql> use test;Database changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| sysc           |+----------------+1 row in set (0.00 sec)mysql> select * from sysc;+------+------+| id   | name |+------+------+| 1001 | 1001 || 1002 | 1002 |+------+------+2 rows in set (0.00 sec)

整理自网络

Svoid
2014-07-16

0 0