since i've broken down the replication enviornment by "reset master;" yesterday.therefore,i'd like to reconfigure it again,there're several simple steps below:
I. Master node operations:
1.Check the necessary parameter is surely spedified.
(root@localhost mysql3306.sock)[(none)]04:04:00>show variables like 'server_id';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| server_id | 1023306 |
+---------------+---------+
1 row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]04:04:22>show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]04:04:22>show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.00 sec) This two parameter below is not necessary but still recommended: (root@localhost mysql3306.sock)[(none)]04:04:23>show variables like 'enforce_gtid_consistency';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON |
+--------------------------+-------+
1 row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]04:04:30>show variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.00 sec) (root@localhost mysql3306.sock)[(none)]04:04:31> 2.Create replication user and grant imperative privileges.
(root@localhost mysql3306.sock)[(none)]04:12:49>create user 'repl'@'%' identified by 'repl4slave';
Query OK, 0 rows affected (0.01 sec) (root@localhost mysql3306.sock)[(none)]04:12:57>grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.00 sec) (root@localhost mysql3306.sock)[(none)]04:13:06> 3.Backup the full database of master by mysqldump and send to the purpose Slave node.
[root@zlm3 04:20:52 /data/backup]
#pwd
/data/backup [root@zlm3 04:23:54 /data/backup]
#/usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -p --master-data=2 --single-transaction -A > db3306-`date +%Y%m%d`.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [root@zlm3 04:24:05 /data/backup]
#ls -l
total 1016
-rw-r--r-- 1 root root 1038595 Jun 6 04:24 db3306-20180606.sql using the "-E","-R" to add events and routines into the dumpfile of backup can eliminate above warning messages.why not using "--trigger" parameter?'cause its default value is "true" in my MySQL version(5.7.21),there's no need to explicitly specify. [root@zlm3 04:24:21 /data/backup]
#/usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -p --master-data=2 --single-transaction -A -E -R > db3306-`date +%Y%m%d`-full.sql
Enter password: [root@zlm3 04:24:32 /data/backup]
#ls -l
total 2036
-rw-r--r-- 1 root root 1040952 Jun 6 04:24 db3306-20180606-full.sql
-rw-r--r-- 1 root root 1038595 Jun 6 04:24 db3306-20180606.sql [root@zlm3 04:24:36 /data/backup]
#scp db3306-20180606-full.sql zlm4:/data/backup
root@zlm4's password:
db3306-20180606-full.sql 100% 1017KB 1.0MB/s 00:00 [root@zlm3 04:24:56 /data/backup]
#
II. Slave node operations:
1.clean the enviornment.(here i'll use the "rm -rf" to delete all the original files on it.)
[root@zlm4 04:36:34 /data/mysql/mysql3306/data]
#pwd
/data/mysql/mysql3306/data [root@zlm4 04:36:37 /data/mysql/mysql3306/data]
#ls -l
total 410524
-rw-r----- 1 mysql mysql 56 Apr 28 14:40 auto.cnf
-rw-r----- 1 mysql mysql 847926 Jun 4 11:23 error.log
-rw-r----- 1 mysql mysql 2144 Jun 4 11:23 ib_buffer_pool
-rw-r----- 1 mysql mysql 104857600 Jun 4 11:23 ibdata1
-rw-r----- 1 mysql mysql 104857600 Jun 4 11:23 ib_logfile0
-rw-r----- 1 mysql mysql 104857600 May 28 03:27 ib_logfile1
-rw-r----- 1 mysql mysql 104857600 Jun 4 11:23 ib_logfile2
-rw-r----- 1 mysql mysql 3821 Apr 28 20:57 innodb_status.5065
-rw-r----- 1 mysql mysql 136 Jun 4 11:23 master.info
drwxr-x--- 2 mysql mysql 4096 Apr 28 14:40 mysql
drwxr-x--- 2 mysql mysql 8192 Apr 28 14:40 performance_schema
-rw-r----- 1 mysql mysql 201 Jun 4 06:49 relay-bin.000063
-rw-r----- 1 mysql mysql 390 Jun 4 11:23 relay-bin.000064
-rw-r----- 1 mysql mysql 38 Jun 4 06:49 relay-bin.index
-rw-r----- 1 mysql mysql 65 Jun 4 11:23 relay-log.info
-rw-r----- 1 mysql mysql 14064 Jun 4 06:46 slow.log
drwxr-x--- 2 mysql mysql 8192 Apr 28 14:40 sys
drwxr-x--- 2 mysql mysql 8192 May 2 04:59 zabbix
drwxr-x--- 2 mysql mysql 97 May 29 04:28 zlm [root@zlm4 04:36:39 /data/mysql/mysql3306/data]
#rm -rf * [root@zlm4 04:36:48 /data/mysql/mysql3306/data]
#ls -l
total 0 [root@zlm4 04:36:51 /data/mysql/mysql3306/data]
#cd .. [root@zlm4 04:36:52 /data/mysql/mysql3306]
#cd logs [root@zlm4 04:37:07 /data/mysql/mysql3306/logs]
#ls -l
total 42944
-rw-r----- 1 mysql mysql 8611664 May 25 11:31 mysql-bin.000015
-rw-r----- 1 mysql mysql 257 May 25 11:31 mysql-bin.000016
-rw-r----- 1 mysql mysql 2019506 May 28 04:49 mysql-bin.000017
-rw-r----- 1 mysql mysql 5654926 May 28 11:37 mysql-bin.000018
-rw-r----- 1 mysql mysql 7148106 May 29 11:27 mysql-bin.000019
-rw-r----- 1 mysql mysql 7010806 May 30 11:29 mysql-bin.000020
-rw-r----- 1 mysql mysql 73339 May 31 03:16 mysql-bin.000021
-rw-r----- 1 mysql mysql 7646943 May 31 11:28 mysql-bin.000022
-rw-r----- 1 mysql mysql 1126469 Jun 1 11:38 mysql-bin.000023
-rw-r----- 1 mysql mysql 4626287 Jun 4 11:23 mysql-bin.000024
-rw-r----- 1 mysql mysql 440 Jun 4 06:46 mysql-bin.index [root@zlm4 04:37:08 /data/mysql/mysql3306/logs]
#rm -f * [root@zlm4 04:37:12 /data/mysql/mysql3306/logs]
#ls -l
total 0 [root@zlm4 04:45:39 /data/mysql/mysql3306/logs]
# 2.Start the mysqld and check the necessary parameter in mysql client.
[root@zlm4 04:34:50 ~]
#sh mysqld.sh [root@zlm4 04:40:50 ~]
#ps aux|grep mysqld
mysql 4012 25.1 15.8 896948 161060 pts/1 Sl 04:40 0:01 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
root 4042 0.0 0.0 112640 960 pts/1 R+ 04:40 0:00 grep --color=auto mysqld [root@zlm4 04:40:55 ~]
#mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) [root@zlm4 04:40:58 ~]
#ps aux|grep mysqld
root 4053 0.0 0.0 112640 960 pts/1 R+ 04:41 0:00 grep --color=auto mysqld The mysqld cannot be started,let's check the "error.log" to findout what has happened. [root@zlm4 04:41:10 ~]
#cd /data/mysql/mysql3306/data/ [root@zlm4 04:41:32 /data/mysql/mysql3306/data]
#ls -l
total 409624
-rw-r----- 1 mysql mysql 56 Jun 6 04:40 auto.cnf
-rw-r----- 1 mysql mysql 9361 Jun 6 04:40 error.log
-rw-r----- 1 mysql mysql 215 Jun 6 04:40 ib_buffer_pool
-rw-r----- 1 mysql mysql 104857600 Jun 6 04:40 ibdata1
-rw-r----- 1 mysql mysql 104857600 Jun 6 04:40 ib_logfile0
-rw-r----- 1 mysql mysql 104857600 Jun 6 04:40 ib_logfile1
-rw-r----- 1 mysql mysql 104857600 Jun 6 04:40 ib_logfile2
-rw-r----- 1 mysql mysql 173 Jun 6 04:40 slow.log [root@zlm4 04:43:05 /data/mysql/mysql3306/data]
#cat error.log|grep ERROR
2018-06-06T02:40:54.506533Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2018-06-06T02:40:54.516986Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2018-06-06T02:40:54.517028Z 0 [ERROR] Aborting [root@zlm4 04:43:15 /data/mysql/mysql3306/data]
# It seems the mysql.user table is indispensable.thus,i'll initialize the db first. [root@zlm4 04:43:15 /data/mysql/mysql3306/data]
#/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --initialize
2018-06-06T02:54:35.627237Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2018-06-06T02:54:35.627308Z 0 [ERROR] Aborting [root@zlm4 04:54:35 /data/mysql/mysql3306/data]
#rm -f * [root@zlm4 04:54:47 /data/mysql/mysql3306/data]
#/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --initialize [root@zlm4 04:54:54 /data/mysql/mysql3306/data]
#ls -l
total 409644
-rw-r----- 1 mysql mysql 56 Jun 6 04:54 auto.cnf
-rw-r----- 1 mysql mysql 984 Jun 6 04:54 error.log
-rw-r----- 1 mysql mysql 420 Jun 6 04:54 ib_buffer_pool
-rw-r----- 1 mysql mysql 104857600 Jun 6 04:54 ibdata1
-rw-r----- 1 mysql mysql 104857600 Jun 6 04:54 ib_logfile0
-rw-r----- 1 mysql mysql 104857600 Jun 6 04:54 ib_logfile1
-rw-r----- 1 mysql mysql 104857600 Jun 6 04:54 ib_logfile2
drwxr-x--- 2 mysql mysql 4096 Jun 6 04:54 mysql
drwxr-x--- 2 mysql mysql 8192 Jun 6 04:54 performance_schema
-rw-r----- 1 mysql mysql 194 Jun 6 04:54 slow.log
drwxr-x--- 2 mysql mysql 8192 Jun 6 04:54 sys [root@zlm4 04:54:58 /data/mysql/mysql3306/data]
#ps aux|grep mysqld
root 4146 0.0 0.0 112640 960 pts/1 R+ 04:55 0:00 grep --color=auto mysqld [root@zlm4 04:55:56 /data/mysql/mysql3306/data]
#/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf &
[1] 4151 [root@zlm4 04:56:11 /data/mysql/mysql3306/data]
#ps aux|grep mysqld
mysql 4151 9.0 17.3 1069544 176676 pts/1 Sl 04:56 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
root 4184 0.0 0.0 112640 956 pts/1 R+ 04:56 0:00 grep --color=auto mysqld [root@zlm4 04:56:14 /data/mysql/mysql3306/data]
# okay,the mysqld process turned to be normal right now,go on. 3.check the necessary parameter.(if they're not correct,modify them)
[root@zlm4 05:01:32 /data/mysql/mysql3306/data]
#mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@zlm4 05:01:47 /data/mysql/mysql3306/data]
#cat error.log | grep temporary password
grep: password: No such file or directory [root@zlm4 05:02:19 /data/mysql/mysql3306/data]
#cat error.log | grep "temporary password"
2018-06-06T02:54:52.457126Z 1 [Note] A temporary password is generated for root@localhost: r?uoNuzqz3oj [root@zlm4 05:02:39 /data/mysql/mysql3306/data]
#mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 5.7.21-log Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost mysql.sock)[(none)]05:02:57>show variables like 'server_id';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
(root@localhost mysql.sock)[(none)]05:08:32> because of the MySQL 5.7 security strategies,using temprary password "r?uoNuzqz3oj" to login after initialization at the first time is imperative and the password must be changed before doing queries. (root@localhost mysql.sock)[(none)]05:12:02>alter user 'root'@'localhost' identified by 'Passw0rd';
Query OK, 0 rows affected (0.00 sec) (root@localhost mysql.sock)[(none)]05:12:36>show variables like 'server_id';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| server_id | 1033306 | --look out,the "server_id" should be different with the one in Maser host.
+---------------+---------+
1 row in set (0.00 sec) (root@localhost mysql.sock)[(none)]05:13:06>show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec) (root@localhost mysql.sock)[(none)]05:13:11>show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.01 sec) (root@localhost mysql.sock)[(none)]05:13:16>show variables like 'enforce_gtid_consistency';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON |
+--------------------------+-------+
1 row in set (0.01 sec) (root@localhost mysql.sock)[(none)]05:13:20>show variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.01 sec) (root@localhost mysql.sock)[(none)]05:13:25> 4. Import the dumpfile of backup received from the Master node
[root@zlm4 06:05:18 ~]
#cd /data/backup [root@zlm4 06:05:22 /data/backup]
#ls -l
total 1020
-rw-r--r-- 1 root root 1040952 Jun 6 04:24 db3306-20180606-full.sql [root@zlm4 06:05:24 /data/backup]
#mysql < db3306-20180606-full.sql
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. [root@zlm4 06:05:47 /data/backup]
#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 446
Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost mysql.sock)[(none)]06:08:54>show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000003 | 398 | | | fc288b24-6934-11e8-9b0e-080027de0e0e:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec) (root@localhost mysql.sock)[(none)]06:09:02>select @@global.gtid_executed;
+----------------------------------------+
| @@global.gtid_executed |
+----------------------------------------+
| fc288b24-6934-11e8-9b0e-080027de0e0e:1 |
+----------------------------------------+
1 row in set (0.00 sec) (root@localhost mysql.sock)[(none)]06:09:21>select @@global.gtid_purged;
+----------------------+
| @@global.gtid_purged |
+----------------------+
| |
+----------------------+
1 row in set (0.00 sec) (root@localhost mysql.sock)[(none)]06:09:33>reset master; -- This command can only be executed on Slave node.
Query OK, 0 rows affected (0.01 sec) (root@localhost mysql.sock)[(none)]06:10:01>select @@global.gtid_executed;
+------------------------+
| @@global.gtid_executed |
+------------------------+
| |
+------------------------+
1 row in set (0.00 sec) (root@localhost mysql.sock)[(none)]06:10:13>exit
Bye [root@zlm4 06:10:22 /data/backup]
#mysql < db3306-20180606-full.sql [root@zlm4 06:10:29 /data/backup]
#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 459
Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost mysql.sock)[(none)]06:10:39>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zlm | -- The database "zlm" in Master node has been restored here.
+--------------------+
5 rows in set (0.00 sec) (root@localhost mysql.sock)[(none)]06:10:43> 5.Execute "change master to ..." command to make the Slave node become the real Slave DB server of the Master node.
(root@localhost mysql.sock)[(none)]06:10:43>change master to \
-> master_host='zlm3',\
-> master_port=3306,\
-> master_user='repl',\
-> master_password='repl4slave',\
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec) (root@localhost mysql.sock)[(none)]06:15:59>start slave;
Query OK, 0 rows affected (0.01 sec) (root@localhost mysql.sock)[(none)]06:16:12>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: zlm3
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 595
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000003
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: 595
Relay_Log_Space: 615
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: 1023306
Master_UUID: 5c77c31b-4add-11e8-81e2-080027de0e0e
Master_Info_File: /data/mysql/mysql3306/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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: 5c77c31b-4add-11e8-81e2-080027de0e0e:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec) (root@localhost mysql.sock)[(none)]06:16:18> Eventually,the Master-Slave replication based on GTID has been accomplished now.