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 1.Check the necessary parameter is surely spedified. 2 (root@localhost mysql3306.sock)[(none)]04:04:00>show variables like 'server_id'; 3 +---------------+---------+ 4 | Variable_name | Value | 5 +---------------+---------+ 6 | server_id | 1023306 | 7 +---------------+---------+ 8 1 row in set (0.00 sec) 9 10 (root@localhost mysql3306.sock)[(none)]04:04:22>show variables like 'log_bin'; 11 +---------------+-------+ 12 | Variable_name | Value | 13 +---------------+-------+ 14 | log_bin | ON | 15 +---------------+-------+ 16 1 row in set (0.00 sec) 17 18 (root@localhost mysql3306.sock)[(none)]04:04:22>show variables like 'gtid_mode'; 19 +---------------+-------+ 20 | Variable_name | Value | 21 +---------------+-------+ 22 | gtid_mode | ON | 23 +---------------+-------+ 24 1 row in set (0.00 sec) 25 26 This two parameter below is not necessary but still recommended: 27 28 (root@localhost mysql3306.sock)[(none)]04:04:23>show variables like 'enforce_gtid_consistency'; 29 +--------------------------+-------+ 30 | Variable_name | Value | 31 +--------------------------+-------+ 32 | enforce_gtid_consistency | ON | 33 +--------------------------+-------+ 34 1 row in set (0.00 sec) 35 36 (root@localhost mysql3306.sock)[(none)]04:04:30>show variables like 'log_slave_updates'; 37 +-------------------+-------+ 38 | Variable_name | Value | 39 +-------------------+-------+ 40 | log_slave_updates | ON | 41 +-------------------+-------+ 42 1 row in set (0.00 sec) 43 44 (root@localhost mysql3306.sock)[(none)]04:04:31> 45 46 2.Create replication user and grant imperative privileges. 47 (root@localhost mysql3306.sock)[(none)]04:12:49>create user 'repl'@'%' identified by 'repl4slave'; 48 Query OK, 0 rows affected (0.01 sec) 49 50 (root@localhost mysql3306.sock)[(none)]04:12:57>grant replication slave on *.* to 'repl'@'%'; 51 Query OK, 0 rows affected (0.00 sec) 52 53 (root@localhost mysql3306.sock)[(none)]04:13:06> 54 55 3.Backup the full database of master by mysqldump and send to the purpose Slave node. 56 [root@zlm3 04:20:52 /data/backup] 57 #pwd 58 /data/backup 59 60 [root@zlm3 04:23:54 /data/backup] 61 #/usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -p --master-data=2 --single-transaction -A > db3306-`date +%Y%m%d`.sql 62 Enter password: 63 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. 64 65 [root@zlm3 04:24:05 /data/backup] 66 #ls -l 67 total 1016 68 -rw-r--r-- 1 root root 1038595 Jun 6 04:24 db3306-20180606.sql 69 70 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. 71 72 [root@zlm3 04:24:21 /data/backup] 73 #/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 74 Enter password: 75 76 [root@zlm3 04:24:32 /data/backup] 77 #ls -l 78 total 2036 79 -rw-r--r-- 1 root root 1040952 Jun 6 04:24 db3306-20180606-full.sql 80 -rw-r--r-- 1 root root 1038595 Jun 6 04:24 db3306-20180606.sql 81 82 [root@zlm3 04:24:36 /data/backup] 83 #scp db3306-20180606-full.sql zlm4:/data/backup 84 root@zlm4's password: 85 db3306-20180606-full.sql 100% 1017KB 1.0MB/s 00:00 86 87 [root@zlm3 04:24:56 /data/backup] 88 #
II. Slave node operations:
1 1.clean the enviornment.(here i'll use the "rm -rf" to delete all the original files on it.) 2 [root@zlm4 04:36:34 /data/mysql/mysql3306/data] 3 #pwd 4 /data/mysql/mysql3306/data 5 6 [root@zlm4 04:36:37 /data/mysql/mysql3306/data] 7 #ls -l 8 total 410524 9 -rw-r----- 1 mysql mysql 56 Apr 28 14:40 auto.cnf 10 -rw-r----- 1 mysql mysql 847926 Jun 4 11:23 error.log 11 -rw-r----- 1 mysql mysql 2144 Jun 4 11:23 ib_buffer_pool 12 -rw-r----- 1 mysql mysql 104857600 Jun 4 11:23 ibdata1 13 -rw-r----- 1 mysql mysql 104857600 Jun 4 11:23 ib_logfile0 14 -rw-r----- 1 mysql mysql 104857600 May 28 03:27 ib_logfile1 15 -rw-r----- 1 mysql mysql 104857600 Jun 4 11:23 ib_logfile2 16 -rw-r----- 1 mysql mysql 3821 Apr 28 20:57 innodb_status.5065 17 -rw-r----- 1 mysql mysql 136 Jun 4 11:23 master.info 18 drwxr-x--- 2 mysql mysql 4096 Apr 28 14:40 mysql 19 drwxr-x--- 2 mysql mysql 8192 Apr 28 14:40 performance_schema 20 -rw-r----- 1 mysql mysql 201 Jun 4 06:49 relay-bin.000063 21 -rw-r----- 1 mysql mysql 390 Jun 4 11:23 relay-bin.000064 22 -rw-r----- 1 mysql mysql 38 Jun 4 06:49 relay-bin.index 23 -rw-r----- 1 mysql mysql 65 Jun 4 11:23 relay-log.info 24 -rw-r----- 1 mysql mysql 14064 Jun 4 06:46 slow.log 25 drwxr-x--- 2 mysql mysql 8192 Apr 28 14:40 sys 26 drwxr-x--- 2 mysql mysql 8192 May 2 04:59 zabbix 27 drwxr-x--- 2 mysql mysql 97 May 29 04:28 zlm 28 29 [root@zlm4 04:36:39 /data/mysql/mysql3306/data] 30 #rm -rf * 31 32 [root@zlm4 04:36:48 /data/mysql/mysql3306/data] 33 #ls -l 34 total 0 35 36 [root@zlm4 04:36:51 /data/mysql/mysql3306/data] 37 #cd .. 38 39 [root@zlm4 04:36:52 /data/mysql/mysql3306] 40 #cd logs 41 42 [root@zlm4 04:37:07 /data/mysql/mysql3306/logs] 43 #ls -l 44 total 42944 45 -rw-r----- 1 mysql mysql 8611664 May 25 11:31 mysql-bin.000015 46 -rw-r----- 1 mysql mysql 257 May 25 11:31 mysql-bin.000016 47 -rw-r----- 1 mysql mysql 2019506 May 28 04:49 mysql-bin.000017 48 -rw-r----- 1 mysql mysql 5654926 May 28 11:37 mysql-bin.000018 49 -rw-r----- 1 mysql mysql 7148106 May 29 11:27 mysql-bin.000019 50 -rw-r----- 1 mysql mysql 7010806 May 30 11:29 mysql-bin.000020 51 -rw-r----- 1 mysql mysql 73339 May 31 03:16 mysql-bin.000021 52 -rw-r----- 1 mysql mysql 7646943 May 31 11:28 mysql-bin.000022 53 -rw-r----- 1 mysql mysql 1126469 Jun 1 11:38 mysql-bin.000023 54 -rw-r----- 1 mysql mysql 4626287 Jun 4 11:23 mysql-bin.000024 55 -rw-r----- 1 mysql mysql 440 Jun 4 06:46 mysql-bin.index 56 57 [root@zlm4 04:37:08 /data/mysql/mysql3306/logs] 58 #rm -f * 59 60 [root@zlm4 04:37:12 /data/mysql/mysql3306/logs] 61 #ls -l 62 total 0 63 64 [root@zlm4 04:45:39 /data/mysql/mysql3306/logs] 65 # 66 67 2.Start the mysqld and check the necessary parameter in mysql client. 68 [root@zlm4 04:34:50 ~] 69 #sh mysqld.sh 70 71 [root@zlm4 04:40:50 ~] 72 #ps aux|grep mysqld 73 mysql 4012 25.1 15.8 896948 161060 pts/1 Sl 04:40 0:01 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf 74 root 4042 0.0 0.0 112640 960 pts/1 R+ 04:40 0:00 grep --color=auto mysqld 75 76 [root@zlm4 04:40:55 ~] 77 #mysql 78 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) 79 80 [root@zlm4 04:40:58 ~] 81 #ps aux|grep mysqld 82 root 4053 0.0 0.0 112640 960 pts/1 R+ 04:41 0:00 grep --color=auto mysqld 83 84 The mysqld cannot be started,let's check the "error.log" to findout what has happened. 85 86 [root@zlm4 04:41:10 ~] 87 #cd /data/mysql/mysql3306/data/ 88 89 [root@zlm4 04:41:32 /data/mysql/mysql3306/data] 90 #ls -l 91 total 409624 92 -rw-r----- 1 mysql mysql 56 Jun 6 04:40 auto.cnf 93 -rw-r----- 1 mysql mysql 9361 Jun 6 04:40 error.log 94 -rw-r----- 1 mysql mysql 215 Jun 6 04:40 ib_buffer_pool 95 -rw-r----- 1 mysql mysql 104857600 Jun 6 04:40 ibdata1 96 -rw-r----- 1 mysql mysql 104857600 Jun 6 04:40 ib_logfile0 97 -rw-r----- 1 mysql mysql 104857600 Jun 6 04:40 ib_logfile1 98 -rw-r----- 1 mysql mysql 104857600 Jun 6 04:40 ib_logfile2 99 -rw-r----- 1 mysql mysql 173 Jun 6 04:40 slow.log 100 101 [root@zlm4 04:43:05 /data/mysql/mysql3306/data] 102 #cat error.log|grep ERROR 103 2018-06-06T02:40:54.506533Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 104 2018-06-06T02:40:54.516986Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 105 2018-06-06T02:40:54.517028Z 0 [ERROR] Aborting 106 107 [root@zlm4 04:43:15 /data/mysql/mysql3306/data] 108 # 109 110 It seems the mysql.user table is indispensable.thus,i'll initialize the db first. 111 112 [root@zlm4 04:43:15 /data/mysql/mysql3306/data] 113 #/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --initialize 114 2018-06-06T02:54:35.627237Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting. 115 2018-06-06T02:54:35.627308Z 0 [ERROR] Aborting 116 117 118 [root@zlm4 04:54:35 /data/mysql/mysql3306/data] 119 #rm -f * 120 121 [root@zlm4 04:54:47 /data/mysql/mysql3306/data] 122 #/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --initialize 123 124 [root@zlm4 04:54:54 /data/mysql/mysql3306/data] 125 #ls -l 126 total 409644 127 -rw-r----- 1 mysql mysql 56 Jun 6 04:54 auto.cnf 128 -rw-r----- 1 mysql mysql 984 Jun 6 04:54 error.log 129 -rw-r----- 1 mysql mysql 420 Jun 6 04:54 ib_buffer_pool 130 -rw-r----- 1 mysql mysql 104857600 Jun 6 04:54 ibdata1 131 -rw-r----- 1 mysql mysql 104857600 Jun 6 04:54 ib_logfile0 132 -rw-r----- 1 mysql mysql 104857600 Jun 6 04:54 ib_logfile1 133 -rw-r----- 1 mysql mysql 104857600 Jun 6 04:54 ib_logfile2 134 drwxr-x--- 2 mysql mysql 4096 Jun 6 04:54 mysql 135 drwxr-x--- 2 mysql mysql 8192 Jun 6 04:54 performance_schema 136 -rw-r----- 1 mysql mysql 194 Jun 6 04:54 slow.log 137 drwxr-x--- 2 mysql mysql 8192 Jun 6 04:54 sys 138 139 [root@zlm4 04:54:58 /data/mysql/mysql3306/data] 140 #ps aux|grep mysqld 141 root 4146 0.0 0.0 112640 960 pts/1 R+ 04:55 0:00 grep --color=auto mysqld 142 143 [root@zlm4 04:55:56 /data/mysql/mysql3306/data] 144 #/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf & 145 [1] 4151 146 147 [root@zlm4 04:56:11 /data/mysql/mysql3306/data] 148 #ps aux|grep mysqld 149 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 150 root 4184 0.0 0.0 112640 956 pts/1 R+ 04:56 0:00 grep --color=auto mysqld 151 152 [root@zlm4 04:56:14 /data/mysql/mysql3306/data] 153 # 154 155 okay,the mysqld process turned to be normal right now,go on. 156 157 3.check the necessary parameter.(if they're not correct,modify them) 158 [root@zlm4 05:01:32 /data/mysql/mysql3306/data] 159 #mysql 160 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 161 162 [root@zlm4 05:01:47 /data/mysql/mysql3306/data] 163 #cat error.log | grep temporary password 164 grep: password: No such file or directory 165 166 [root@zlm4 05:02:19 /data/mysql/mysql3306/data] 167 #cat error.log | grep "temporary password" 168 2018-06-06T02:54:52.457126Z 1 [Note] A temporary password is generated for root@localhost: r?uoNuzqz3oj 169 170 [root@zlm4 05:02:39 /data/mysql/mysql3306/data] 171 #mysql -p 172 Enter password: 173 Welcome to the MySQL monitor. Commands end with ; or g. 174 Your MySQL connection id is 44 175 Server version: 5.7.21-log 176 177 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 178 179 Oracle is a registered trademark of Oracle Corporation and/or its 180 affiliates. Other names may be trademarks of their respective 181 owners. 182 183 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 184 185 (root@localhost mysql.sock)[(none)]05:02:57>show variables like 'server_id'; 186 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. 187 (root@localhost mysql.sock)[(none)]05:08:32> 188 189 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. 190 191 (root@localhost mysql.sock)[(none)]05:12:02>alter user 'root'@'localhost' identified by 'Passw0rd'; 192 Query OK, 0 rows affected (0.00 sec) 193 194 (root@localhost mysql.sock)[(none)]05:12:36>show variables like 'server_id'; 195 +---------------+---------+ 196 | Variable_name | Value | 197 +---------------+---------+ 198 | server_id | 1033306 | --look out,the "server_id" should be different with the one in Maser host. 199 +---------------+---------+ 200 1 row in set (0.00 sec) 201 202 (root@localhost mysql.sock)[(none)]05:13:06>show variables like 'log_bin'; 203 +---------------+-------+ 204 | Variable_name | Value | 205 +---------------+-------+ 206 | log_bin | ON | 207 +---------------+-------+ 208 1 row in set (0.00 sec) 209 210 (root@localhost mysql.sock)[(none)]05:13:11>show variables like 'gtid_mode'; 211 +---------------+-------+ 212 | Variable_name | Value | 213 +---------------+-------+ 214 | gtid_mode | ON | 215 +---------------+-------+ 216 1 row in set (0.01 sec) 217 218 (root@localhost mysql.sock)[(none)]05:13:16>show variables like 'enforce_gtid_consistency'; 219 +--------------------------+-------+ 220 | Variable_name | Value | 221 +--------------------------+-------+ 222 | enforce_gtid_consistency | ON | 223 +--------------------------+-------+ 224 1 row in set (0.01 sec) 225 226 (root@localhost mysql.sock)[(none)]05:13:20>show variables like 'log_slave_updates'; 227 +-------------------+-------+ 228 | Variable_name | Value | 229 +-------------------+-------+ 230 | log_slave_updates | ON | 231 +-------------------+-------+ 232 1 row in set (0.01 sec) 233 234 (root@localhost mysql.sock)[(none)]05:13:25> 235 236 4. Import the dumpfile of backup received from the Master node 237 [root@zlm4 06:05:18 ~] 238 #cd /data/backup 239 240 [root@zlm4 06:05:22 /data/backup] 241 #ls -l 242 total 1020 243 -rw-r--r-- 1 root root 1040952 Jun 6 04:24 db3306-20180606-full.sql 244 245 [root@zlm4 06:05:24 /data/backup] 246 #mysql < db3306-20180606-full.sql 247 ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. 248 249 [root@zlm4 06:05:47 /data/backup] 250 #mysql 251 Welcome to the MySQL monitor. Commands end with ; or g. 252 Your MySQL connection id is 446 253 Server version: 5.7.21-log MySQL Community Server (GPL) 254 255 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 256 257 Oracle is a registered trademark of Oracle Corporation and/or its 258 affiliates. Other names may be trademarks of their respective 259 owners. 260 261 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 262 263 (root@localhost mysql.sock)[(none)]06:08:54>show master status; 264 +------------------+----------+--------------+------------------+----------------------------------------+ 265 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 266 +------------------+----------+--------------+------------------+----------------------------------------+ 267 | mysql-bin.000003 | 398 | | | fc288b24-6934-11e8-9b0e-080027de0e0e:1 | 268 +------------------+----------+--------------+------------------+----------------------------------------+ 269 1 row in set (0.00 sec) 270 271 (root@localhost mysql.sock)[(none)]06:09:02>select @@global.gtid_executed; 272 +----------------------------------------+ 273 | @@global.gtid_executed | 274 +----------------------------------------+ 275 | fc288b24-6934-11e8-9b0e-080027de0e0e:1 | 276 +----------------------------------------+ 277 1 row in set (0.00 sec) 278 279 (root@localhost mysql.sock)[(none)]06:09:21>select @@global.gtid_purged; 280 +----------------------+ 281 | @@global.gtid_purged | 282 +----------------------+ 283 | | 284 +----------------------+ 285 1 row in set (0.00 sec) 286 287 (root@localhost mysql.sock)[(none)]06:09:33>reset master; -- This command can only be executed on Slave node. 288 Query OK, 0 rows affected (0.01 sec) 289 290 (root@localhost mysql.sock)[(none)]06:10:01>select @@global.gtid_executed; 291 +------------------------+ 292 | @@global.gtid_executed | 293 +------------------------+ 294 | | 295 +------------------------+ 296 1 row in set (0.00 sec) 297 298 (root@localhost mysql.sock)[(none)]06:10:13>exit 299 Bye 300 301 [root@zlm4 06:10:22 /data/backup] 302 #mysql < db3306-20180606-full.sql 303 304 [root@zlm4 06:10:29 /data/backup] 305 #mysql 306 Welcome to the MySQL monitor. Commands end with ; or g. 307 Your MySQL connection id is 459 308 Server version: 5.7.21-log MySQL Community Server (GPL) 309 310 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 311 312 Oracle is a registered trademark of Oracle Corporation and/or its 313 affiliates. Other names may be trademarks of their respective 314 owners. 315 316 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 317 318 (root@localhost mysql.sock)[(none)]06:10:39>show databases; 319 +--------------------+ 320 | Database | 321 +--------------------+ 322 | information_schema | 323 | mysql | 324 | performance_schema | 325 | sys | 326 | zlm | -- The database "zlm" in Master node has been restored here. 327 +--------------------+ 328 5 rows in set (0.00 sec) 329 330 (root@localhost mysql.sock)[(none)]06:10:43> 331 332 5.Execute "change master to ..." command to make the Slave node become the real Slave DB server of the Master node. 333 (root@localhost mysql.sock)[(none)]06:10:43>change master to 334 -> master_host='zlm3', 335 -> master_port=3306, 336 -> master_user='repl', 337 -> master_password='repl4slave', 338 -> master_auto_position=1; 339 Query OK, 0 rows affected, 2 warnings (0.03 sec) 340 341 (root@localhost mysql.sock)[(none)]06:15:59>start slave; 342 Query OK, 0 rows affected (0.01 sec) 343 344 (root@localhost mysql.sock)[(none)]06:16:12>show slave statusG 345 *************************** 1. row *************************** 346 Slave_IO_State: Waiting for master to send event 347 Master_Host: zlm3 348 Master_User: repl 349 Master_Port: 3306 350 Connect_Retry: 60 351 Master_Log_File: mysql-bin.000003 352 Read_Master_Log_Pos: 595 353 Relay_Log_File: relay-bin.000002 354 Relay_Log_Pos: 414 355 Relay_Master_Log_File: mysql-bin.000003 356 Slave_IO_Running: Yes 357 Slave_SQL_Running: Yes 358 Replicate_Do_DB: 359 Replicate_Ignore_DB: 360 Replicate_Do_Table: 361 Replicate_Ignore_Table: 362 Replicate_Wild_Do_Table: 363 Replicate_Wild_Ignore_Table: 364 Last_Errno: 0 365 Last_Error: 366 Skip_Counter: 0 367 Exec_Master_Log_Pos: 595 368 Relay_Log_Space: 615 369 Until_Condition: None 370 Until_Log_File: 371 Until_Log_Pos: 0 372 Master_SSL_Allowed: No 373 Master_SSL_CA_File: 374 Master_SSL_CA_Path: 375 Master_SSL_Cert: 376 Master_SSL_Cipher: 377 Master_SSL_Key: 378 Seconds_Behind_Master: 0 379 Master_SSL_Verify_Server_Cert: No 380 Last_IO_Errno: 0 381 Last_IO_Error: 382 Last_SQL_Errno: 0 383 Last_SQL_Error: 384 Replicate_Ignore_Server_Ids: 385 Master_Server_Id: 1023306 386 Master_UUID: 5c77c31b-4add-11e8-81e2-080027de0e0e 387 Master_Info_File: /data/mysql/mysql3306/data/master.info 388 SQL_Delay: 0 389 SQL_Remaining_Delay: NULL 390 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 391 Master_Retry_Count: 86400 392 Master_Bind: 393 Last_IO_Error_Timestamp: 394 Last_SQL_Error_Timestamp: 395 Master_SSL_Crl: 396 Master_SSL_Crlpath: 397 Retrieved_Gtid_Set: 398 Executed_Gtid_Set: 5c77c31b-4add-11e8-81e2-080027de0e0e:1-2 399 Auto_Position: 1 400 Replicate_Rewrite_DB: 401 Channel_Name: 402 Master_TLS_Version: 403 1 row in set (0.00 sec) 404 405 (root@localhost mysql.sock)[(none)]06:16:18> 406 407 Eventually,the Master-Slave replication based on GTID has been accomplished now.