bt种子下载 成人,峰回路转造句,男人的大几巴图片
Preface
Hostname | IP/Port | Identity | OS Version | MySQL Version | GTID Mode | Binlog Format |
zlm2 | 192.168.1.101/3306 | master | CentOS 7.0 | 5.7.21 | off | row |
zlm3 | 192.168.1.102/3306 | slave | CentOS 7.0 | 5.7.21 | off | row |
1 //Master 2 (root@localhost mysql3306.sock)[(none)]>show variables like 'gtid_mode'; 3 +---------------+-------+ 4 | Variable_name | Value | 5 +---------------+-------+ 6 | gtid_mode | OFF | 7 +---------------+-------+ 8 1 row in set (0.01 sec 9 10 //Slave 11 (root@localhost mysql3306.sock)[(none)]>show variables like 'gtid_mode'; 12 +---------------+-------+ 13 | Variable_name | Value | 14 +---------------+-------+ 15 | gtid_mode | OFF | 16 +---------------+-------+ 17 1 row in set (0.00 sec)
1 [root@zlm2 07:22:53 ~/sysbench-1.0/src/lua] 2 #sysbench oltp_insert.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=10 --table-size=100000 --mysql-storage-engine=innodb prepare 3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2) 4 5 Creating table 'sbtest1'... 6 Inserting 100000 records into 'sbtest1' 7 Creating a secondary index on 'sbtest1'... 8 Creating table 'sbtest2'... 9 Inserting 100000 records into 'sbtest2' 10 Creating a secondary index on 'sbtest2'... 11 ... 12 13 [root@zlm2 07:26:30 ~/sysbench-1.0/src/lua] 14 #sysbench oltp_insert.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --threads=3 --time=7200 --report-interval=60 --rand-type=uniform run 15 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2) 16 17 Running the test with following options: 18 Number of threads: 3 19 Report intermediate results every 60 second(s) 20 Initializing random number generator from current time 21 22 23 Initializing worker threads... 24 25 Threads started! 26 27 [ 60s ] thds: 3 tps: 1623.71 qps: 1623.71 (r/w/o: 0.00/1623.71/0.00) lat (ms,95%): 2.97 err/s: 0.00 reconn/s: 0.00 28 [ 120s ] thds: 3 tps: 1844.96 qps: 1844.96 (r/w/o: 0.00/1844.96/0.00) lat (ms,95%): 2.61 err/s: 0.00 reconn/s: 0.00 29 [ 180s ] thds: 3 tps: 1894.37 qps: 1894.37 (r/w/o: 0.00/1894.37/0.00) lat (ms,95%): 2.61 err/s: 0.00 reconn/s: 0.00 30 ... 31 32 //Check the output of processlist. 33 (root@localhost mysql3306.sock)[(none)]>show processlist; 34 +----+------+------------+----------+-------------+------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ 35 | Id | User | Host | db | Command | Time | State | Info | 36 +----+------+------------+----------+-------------+------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ 37 | 41 | root | localhost | NULL | Query | 0 | starting | show processlist | 38 | 43 | repl | zlm3:44252 | NULL | Binlog Dump | 379 | Master has sent all binlog to slave; waiting for more updates | NULL | 39 | 44 | zlm | zlm2:56708 | sysbench | Query | 0 | update | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 8106, '57837919367-24452778030-14591605115-8049012633 | 40 | 45 | zlm | zlm2:56709 | sysbench | Query | 0 | update | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5602, '45087463438-93604980565-67881991526-9944080034 | 41 | 46 | zlm | zlm2:56710 | sysbench | Query | 0 | update | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 3497, '01822437471-94427682076-39418270545-9867829936 | 42 +----+------+------------+----------+-------------+------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+ 43 5 rows in set (0.00 sec)
1 (root@localhost mysql3306.sock)[(none)]>show slave status\G 2 *************************** 1. row *************************** 3 Slave_IO_State: Waiting for master to send event 4 Master_Host: 192.168.1.101 5 Master_User: repl 6 Master_Port: 3306 7 Connect_Retry: 60 8 Master_Log_File: mysql-bin.000006 9 Read_Master_Log_Pos: 191183208 10 Relay_Log_File: relay-bin.000023 11 Relay_Log_Pos: 41556833 12 Relay_Master_Log_File: mysql-bin.000006 13 Slave_IO_Running: Yes 14 Slave_SQL_Running: Yes 15 Replicate_Do_DB: 16 Replicate_Ignore_DB: 17 Replicate_Do_Table: 18 Replicate_Ignore_Table: 19 Replicate_Wild_Do_Table: 20 Replicate_Wild_Ignore_Table: 21 Last_Errno: 0 22 Last_Error: 23 Skip_Counter: 0 24 Exec_Master_Log_Pos: 175774368 25 Relay_Log_Space: 191183725 26 Until_Condition: None 27 Until_Log_File: 28 Until_Log_Pos: 0 29 Master_SSL_Allowed: No 30 Master_SSL_CA_File: 31 Master_SSL_CA_Path: 32 Master_SSL_Cert: 33 Master_SSL_Cipher: 34 Master_SSL_Key: 35 Seconds_Behind_Master: 20 36 Master_SSL_Verify_Server_Cert: No 37 Last_IO_Errno: 0 38 Last_IO_Error: 39 Last_SQL_Errno: 0 40 Last_SQL_Error: 41 Replicate_Ignore_Server_Ids: 42 Master_Server_Id: 1013306 43 Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e 44 Master_Info_File: mysql.slave_master_info 45 SQL_Delay: 0 46 SQL_Remaining_Delay: NULL 47 Slave_SQL_Running_State: System lock 48 Master_Retry_Count: 86400 49 Master_Bind: 50 Last_IO_Error_Timestamp: 51 Last_SQL_Error_Timestamp: 52 Master_SSL_Crl: 53 Master_SSL_Crlpath: 54 Retrieved_Gtid_Set: 55 Executed_Gtid_Set: 56 Auto_Position: 0 //This means we are using the classic replication now. 57 Replicate_Rewrite_DB: 58 Channel_Name: 59 Master_TLS_Version: 60 1 row in set (0.00 sec)
1 //Master 2 (root@localhost mysql3306.sock)[(none)]>set @@global.enforce_gtid_consistency=warn; 3 Query OK, 0 rows affected (0.13 sec) 4 5 (root@localhost mysql3306.sock)[(none)]>select @@global.enforce_gtid_consistency; 6 +-----------------------------------+ 7 | @@global.enforce_gtid_consistency | 8 +-----------------------------------+ 9 | WARN | 10 +-----------------------------------+ 11 1 row in set (0.06 sec) 12 13 //Error log of master 14 2018-07-13T07:37:56.877416+01:00 47 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN. 15 2018-07-13T07:39:15.748645+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8825ms. The settings might not be optimal. (flushed=2001 and evicted=0, during the time.) 16 17 //Slave 18 (root@localhost mysql3306.sock)[(none)]>set @@global.enforce_gtid_consistency=warn; 19 Query OK, 0 rows affected (0.49 sec) 20 21 (root@localhost mysql3306.sock)[(none)]>select @@global.enforce_gtid_consistency; 22 +-----------------------------------+ 23 | @@global.enforce_gtid_consistency | 24 +-----------------------------------+ 25 | WARN | 26 +-----------------------------------+ 27 1 row in set (1.35 sec) 28 29 //Error log of slave 30 2018-07-13T07:38:02.556232+01:00 27 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN. 31 32 //Make sure there's no warning messages on both master and slave.
1 //Master 2 (root@localhost mysql3306.sock)[(none)]>set @@global.enforce_gtid_consistency=on; 3 Query OK, 0 rows affected (0.00 sec) 4 5 (root@localhost mysql3306.sock)[(none)]>select @@global.enforce_gtid_consistency; 6 +-----------------------------------+ 7 | @@global.enforce_gtid_consistency | 8 +-----------------------------------+ 9 | ON | 10 +-----------------------------------+ 11 1 row in set (0.00 sec) 12 13 //Slave 14 (root@localhost mysql3306.sock)[(none)]>set @@global.enforce_gtid_consistency=on; 15 Query OK, 0 rows affected (0.03 sec) 16 17 (root@localhost mysql3306.sock)[(none)]>select @@global.enforce_gtid_consistency; 18 +-----------------------------------+ 19 | @@global.enforce_gtid_consistency | 20 +-----------------------------------+ 21 | ON | 22 +-----------------------------------+ 23 1 row in set (0.00 sec)
1 //Master 2 (root@localhost mysql3306.sock)[(none)]>set @@globa.gtid_mode=off_permissive; 3 Query OK, 0 rows affected (0.72 sec) 4 5 (root@localhost mysql3306.sock)[(none)]>select @@global.gtid_mode; 6 +--------------------+ 7 | @@global.gtid_mode | 8 +--------------------+ 9 | OFF_PERMISSIVE | 10 +--------------------+ 11 1 row in set (0.01 sec) 12 13 //Error log of master 14 2018-07-13T07:37:56.877416+01:00 47 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN. 15 2018-07-13T07:39:15.748645+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8825ms. The settings might not be optimal. (flushed=2001 and evicted=0, during the time.) 16 2018-07-13T07:42:38.472436+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8569ms. The settings might not be optimal. (flushed=2001 and evicted=0, during the time.) 17 2018-07-13T07:44:03.886312+01:00 47 [Note] Changed ENFORCE_GTID_CONSISTENCY from WARN to ON. 18 2018-07-13T07:48:04.137251+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5067ms. The settings might not be optimal. (flushed=713 and evicted=0, during the time.) 19 2018-07-13T07:48:39.586306+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5394ms. The settings might not be optimal. (flushed=704 and evicted=0, during the time.) 20 2018-07-13T07:49:38.441594+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4927ms. The settings might not be optimal. (flushed=709 and evicted=0, during the time.) 21 2018-07-13T07:50:19.070954+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4539ms. The settings might not be optimal. (flushed=721 and evicted=0, during the time.) 22 2018-07-13T07:50:20.930564+01:00 47 [Note] Changed GTID_MODE from OFF to OFF_PERMISSIVE. 23 2018-07-13T07:50:36.490470+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4602ms. The settings might not be optimal. (flushed=705 and evicted=0, during the time.) 24 25 26 //Slave 27 (root@localhost mysql3306.sock)[(none)]>set @@global.gtid_mode=off_permissive; 28 Query OK, 0 rows affected (3.02 sec) 29 30 (root@localhost mysql3306.sock)[(none)]>select @@global.gtid_mode; 31 +--------------------+ 32 | @@global.gtid_mode | 33 +--------------------+ 34 | OFF_PERMISSIVE | 35 +--------------------+ 36 1 row in set (0.00 sec) 37 38 //Error log of slave 39 2018-07-13T07:38:02.556232+01:00 27 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN. 40 2018-07-13T07:44:22.628014+01:00 27 [Note] Changed ENFORCE_GTID_CONSISTENCY from WARN to ON. 41 2018-07-13T07:49:33.136288+01:00 27 [Note] Aborted connection 27 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets) 42 2018-07-13T07:50:27.360767+01:00 28 [Note] Changed GTID_MODE from OFF to OFF_PERMISSIVE. 43 2018-07-13T07:50:39.972826+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 10489ms. The settings might not be optimal. (flushed=2001 and evicted=0, during the time.)
1 //Master 2 (root@localhost mysql3306.sock)[(none)]>set @@global.gtid_mode=on_permissive; 3 Query OK, 0 rows affected (3.26 sec) 4 5 (root@localhost mysql3306.sock)[(none)]>select @@global.gtid_mode; 6 +--------------------+ 7 | @@global.gtid_mode | 8 +--------------------+ 9 | ON_PERMISSIVE | 10 +--------------------+ 11 1 row in set (0.00 sec) 12 13 //Error log of master 14 2018-07-13T07:57:16.796632+01:00 48 [Note] Changed GTID_MODE from OFF_PERMISSIVE to ON_PERMISSIVE. 15 2018-07-13T07:57:20.034425+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4954ms. The settings might not be optimal. (flushed=752 and evicted=0, during the time.) 16 17 //Slave 18 (root@localhost mysql3306.sock)[(none)]>set @@global.gtid_mode=on_permissive; 19 Query OK, 0 rows affected (2.22 sec) 20 21 (root@localhost mysql3306.sock)[(none)]>select @@global.gtid_mode; 22 +--------------------+ 23 | @@global.gtid_mode | 24 +--------------------+ 25 | ON_PERMISSIVE | 26 +--------------------+ 27 1 row in set (0.06 sec) 28 29 //Error log of slave 30 2018-07-13T07:56:57.921081+01:00 29 [Note] Changed GTID_MODE from OFF_PERMISSIVE to ON_PERMISSIVE. 31 2018-07-13T07:57:03.109628+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5853ms. The settings might not be optimal. (flushed=733 and evicted=0, during the time.) 32 33 //I'm afraid it's better to execut "set gtid_mode=on_permissive;" on slave first for best practice even though sometimes it's not obliged to do that.
1 //Master 2 (root@localhost mysql3306.sock)[(none)]>show status like 'ongoing_anonymous_transaction_count'; 3 +-------------------------------------+-------+ 4 | Variable_name | Value | 5 +-------------------------------------+-------+ 6 | Ongoing_anonymous_transaction_count | 0 | 7 +-------------------------------------+-------+ 8 1 row in set (0.66 sec) 9 10 //Slave 11 (root@localhost mysql3306.sock)[(none)]>show status like 'ongoing_anonymous_transaction_count'; 12 +-------------------------------------+-------+ 13 | Variable_name | Value | 14 +-------------------------------------+-------+ 15 | Ongoing_anonymous_transaction_count | 0 | 16 +-------------------------------------+-------+ 17 1 row in set (3.34 sec) 18 19 //The value of 'ongoing_anonymous_transaction_count' become "0" what means there arn't non-gtid events in binlogs anymore.Therefore,we can do the last step,that is,to change the "gtid_mode" to "on".
1 //Master 2 (root@localhost mysql3306.sock)[(none)]>select @@global.gtid_mode; 3 +--------------------+ 4 | @@global.gtid_mode | 5 +--------------------+ 6 | ON | 7 +--------------------+ 8 1 row in set (0.00 sec) 9 10 //Error log of master 11 2018-07-13T08:20:59.853460+01:00 50 [Note] Changed GTID_MODE from ON_PERMISSIVE to ON. 12 2018-07-13T08:21:01.804678+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6035ms. The settings might not be optimal. (flushed=745 and evicted=0, during the time.) 13 2018-07-13T08:21:56.202081+01:00 43 [Note] Aborted connection 43 to db: 'unconnected' user: 'repl' host: 'zlm3' (Failed on my_net_write()) 14 15 //Slave 16 (root@localhost mysql3306.sock)[(none)]>set @@global.gtid_mode=on; 17 ERROR 2006 (HY000): MySQL server has gone away 18 No connection. Trying to reconnect... 19 Connection id: 31 20 Current database: *** NONE *** 21 22 //It's stuck here.Oh my!!! 23 24 //Check the error log of slave see what has happened. 25 2018-07-13T08:20:49.070915+01:00 25 [ERROR] Disk is full writing './relay-bin.000044' (Errcode: 16026912 - No space left on device). Waiting for someone to free space... 26 2018-07-13T08:20:49.070948+01:00 25 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 27 2018-07-13T08:20:49.104353+01:00 26 [ERROR] Disk is full writing '/data/mysql/mysql3306/logs/mysql-bin.000011' (Errcode: 16026912 - No space left on device). Waiting for someone to free space... 28 2018-07-13T08:20:49.104382+01:00 26 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 29 2018-07-13T08:20:51.712891+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4001ms. The settings might not be optimal. (flushed=742 and evicted=0, during the time.) 30 2018-07-13T08:21:00.346384+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7634ms. The settings might not be optimal. (flushed=2000 and evicted=0, during the time.) 31 32 //It shows "[ERROR] Disk is full writing ... ".The test tables have been inserted too many data. 33 34 [root@zlm3 08:08:06 ~] 35 #df -h 36 Filesystem Size Used Avail Use% Mounted on 37 /dev/mapper/centos-root 8.4G 8.4G 20K 100% / //The root directory is full. 38 devtmpfs 488M 0 488M 0% /dev 39 tmpfs 497M 0 497M 0% /dev/shm 40 tmpfs 497M 6.6M 491M 2% /run 41 tmpfs 497M 0 497M 0% /sys/fs/cgroup 42 /dev/sda1 497M 118M 379M 24% /boot 43 none 87G 80G 7.1G 92% /vagrant 44 45 //Unfortunately,the disk on salve has been writen fully.
1 //The output of "show salve status\G" 2 Last_IO_Errno: 1593 3 Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF 4 5 //You cannot modify "gtid_mode" to "on" directly. 6 (root@localhost mysql3306.sock)[(none)]>set @@global.gtid_mode=on; 7 ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
小白安装登录mysql-8.0.19-winx64的教程图解(新手必看)
Navicat连接MySQL时报10060、1045错误及my.ini位置问题
网友评论