Master-slave replication and cascade of Mysql

Mysql master-slave replication and cascade record the master-slave replication and cascade replication of mysql. I. Environment HOST: 192.25.10.71 HOST: 192.25.10.73 slave: 192.25.10.76DB: Mysql 5.5.31OS: CentOS 6.3 II. Architecture 3. Implementation steps: Installing mysql 1. Creating a Test DB on the master side

[Root @ proxy1 ~] # MysqlWelcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 1959 Server version: 5.5.31-log Source distributionCopyright (c) 2000,201 3, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear t He current input statement. mysql> create database db_kenyon; Query OK, 1 row affected (0.03 sec) mysql> show databases; + rows + | Database | + ------------------ + | information_schema | db_kenyon | mysql | performance_schema | test | + ------------------ + 5 rows in set (0.00 sec) mysql> use db_kenyon; database changedmysql> create table t_kenyon (id int); Query OK, 0 rows affected (0.07 s Ec) mysql> insert into t_kenyon values (1), (2), (3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t_kenyon; + ------ + | id | + ------ + | 1 | 2 | 3 | + ------ + 3 rows in set (0.00 sec) mysql> show master status; Empty set (0.00 sec) [root @ localhost ~] #2. modify/etc/my. cnf [mysqld] log-bin = mysql-binserver-id = 1 binlog-do-db = db_kenyoninnodb_data_home_dir =/usr/local/mysql/data/innodb_log_group_home_dir =/usr/local/mysql /data/innodb_buffer_pool_size = bytes = 20Minnodb_log_file_size = 64Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1innodb_lockwait_timeout = 503. create a Server Load balancer user connected to the master, and restart master [root @ localhos T ~] #/Etc/init. d/mysqld restartShutting down MySQL... SUCCESS! Starting MySQL ...... SUCCESS! [Root @ localhost ~] # Mysqlmysql> grant replication slave on *. * to repl @ '1970. 25.10.73 'identified by '123456'; mysql> flush privileges; [root @ localhost ~] #4. back up the master data and copy it to the slave mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> show master status; + threads + ---------- + --------------- + threads + | File | Position | Binlog_Do_DB | bytes | + -------------------- + ---------- + ------------- + threads + | mysql-bin.000002 | 120 | db_kenyon | | + ------------------ + ---------- + --------------- + -------------------------------------------------- + ----------------- + 1 row in set (0.00 sec) mysql> another session: [root @ localhost log] # cd/usr/local/mysql/data [root @ localhost data] # tar-zcvf backup.tar.gz db_kenyon [root @ localhost] # scp backup.tar.gz root@192.25.10.73: /usr/local/mysql/data/-- return to the previous session and unlock it after the host is set up to ensure data consistency mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) 5. modify slave Data [mysqld] server-id = 2master-host = 192.25.10.71 #5.5 and above discard master-user = repl #5.5 and above discard master-password = 123456 #5.5 and above discard master-port = 3306 # discard master-connect-retry = 60 # retry time 60 seconds, # more than 5.5 discard replicate-do-db = db_kenyon # Database log to be synchronized-slave-updates = 1 # enable slave Database log, in this way, you can set chain replication read-only = 1 #0 to indicate read/write, and 1 to indicate read-only, but only for common users. Super Users and synchronization users are not limited to this 6. restart slave and specify the host to check mysql> change master to master_host = '20180101. 25.10.71 ', master_user = 'repl', master_password = '000000', master_log_file = 'mysql-bin.000002 ', master_log_pos = 120 mysql> show slave status \ G ****************************** 1. row ************************* Slave_IO_State: Master_Host: 192.25.10.71 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 layout: 120 Relay_Log_File: localhost-relay-bin.000001 Relay_Log_Pos: 4 rows: mysql-bin.000002 labels: No rows: db_kenyon labels: Last_Errno: 0 Last_Error: Skip_Counter: 0 condition: 120 Relay_Log_Space: 120 Until_Condition: None Until_Log_File: Unknown: 0 condition: No condition: Unknown: Master_SSL_Cert: Unknown: Master_SSL_Key: Unknown: No condition: 0 keys: Keys: 0 Last_ SQL _Error: Keys: Master_Server_Id: 0 Master_UUID: Master_Info_File:/usr/local/mysql/data/master.info SQL _Delay: 0 keys: NULL keys: 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)

 

Adjust the network and mysql parameters so that Slave_IO_Running and Slave_ SQL _Running are in the YES 7 status. other configurations in the cascade process are shown above. Note that the cascade parameter log-slave-updates is enabled. the value in cnf should be 1, or

mysql> show variables like 'log_slave_%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| log_slave_updates | ON   |+-------------------+-------+1 row in set (0.00 sec)

 

8. The process of the master and slave is based on the master and slave, and then the slave machine is used as the host. You can perform the reverse operation again. In a simple test, you can create a table on Table 71 and view the table on table 73 and table 76. Iv. Cascade Performance Loss Test Using mysqlslap

[root@proxy1 bin]# ./mysqlslap   --concurrency=2  --iterations=5 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-guid-primary --number-of-queries=10000 --auto-generate-sql-load-type=writeBenchmark        Average number of seconds to run all queries: 5.324 seconds        Minimum number of seconds to run all queries: 4.106 seconds        Maximum number of seconds to run all queries: 6.113 seconds        Number of clients running queries: 2        Average number of queries per client: 5000

 

1878 change concurrency in the processing capacity per second. The following result is displayed: Average concurrency time (s) (the latter enables cascade) processing loss per second: 2 5.324 1878 0.6 s 11% 2 5.912 1691 0.6 s 11% 2.521 3966 0.3 s 12% 2.828 3536 0.3 12% s 2.161 4627 0.2 7% s 2.331 4290 0.2 7% 100 s 2.355 4246 0.3 s 11% 100 2.618 3819 s 0.3