MySQL管理工具MySQL Utilities — 使用mysqldbexport与mysqldbimport创建新的从库(10)

前面两节内容说到了mysqldbexport 和 mysqldbimport。通过这两个工具来为现有的主从复制新加一台新的从库服务器。

环境说明

instance_3306： master文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

instance_3308：slave文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

导出数据

# mysqldbexport --server=instance_3306 --all --export=both --rpl=master --rpl-user=root > /tmp/data.sql

导入数据

# mysql -uroot -p -S /data/mydata3308/mysql.sock -e "reset master"
Enter password: 
[root@localhost t]# mysqldbimport --server=instance_3308 /tmp/data.sql
# Source on localhost: ... connected.
# Importing definitions from /tmp/data.sql.
#...done.

至此，主从已经建立好了。是不是非常方便非常容易？文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

主从状态

instance_3306文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

mysql> show variables like "server_uuid";
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 1597393e-8760-11e4-baf3-0050569f3bb8 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|      3308 |      | 3308 |      3306 | 034d694d-8769-11e4-bb2d-0050569f3bb8 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

instance_3308文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

mysql> show variables like "%uuid%";
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 034d694d-8769-11e4-bb2d-0050569f3bb8 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-3306.000002
          Read_Master_Log_Pos: 262554
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 288
        Relay_Master_Log_File: mysql-bin-3306.000002
             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: 262554
              Relay_Log_Space: 461
              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: 3306
                  Master_UUID: 1597393e-8760-11e4-baf3-0050569f3bb8
             Master_Info_File: /data/mydata3308/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)

说明

data.sql文件中会包含如下语句的：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

STOP SLAVE;文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'root', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'mysql-bin-3306.000002', MASTER_LOG_POS = 262554;文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

START SLAVE;文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

在导入之前，先执行下reset master，是为了清除之前的主从信息。否则会报下面的错误。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

ERROR: The import operation contains GTID statements that require the global gtid_executed
system variable on the target to be empty (no value). The gtid_executed value must be reset
by issuing a RESET MASTER command on the target prior to attempting the import operation.
Once the global gtid_executed value is cleared, you may retry the import.文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/

我这里就没创建复制用户，就直接以root用户来的。 实际环境下，不要这么干。我图省事哈。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbexport-mysqldbimport-add-a-new-slave/