bitscn.com
mysql的主从复制和级联
记录一下mysql的主主复制和级联复制。 
一、环境
主机:192.25.10.71
主机:192.25.10.73
从机:192.25.10.76
db:mysql 5.5.31
os:centos 6.3
二、架构
三、实施步骤 
mysql的安装略 
1.master端创建测试db
[root@proxy1 ~]# mysqlwelcome to the mysql monitor. commands end with ; or /g.your mysql connection id is 1959server version: 5.5.31-log source distributioncopyright (c) 2000, 2013, 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 the current input statement.mysql> create database db_kenyon;query ok, 1 row affected (0.03 sec)mysql> show databases;+--------------------+| 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 sec)mysql> insert into t_kenyon values(1),(2),(3);query ok, 3 rows affected (0.01 sec)records: 3 duplicates: 0 warnings: 0mysql> 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.修改配置/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 = 256minnodb_additional_mem_pool_size = 20minnodb_log_file_size = 64minnodb_log_buffer_size = 8minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 503.创建slave端连到master端的用户,重启master[root@localhost ~]# /etc/init.d/mysqld restartshutting down mysql.. success! starting mysql............ success![root@localhost ~]# mysqlmysql>grant replication slave on *.* to repl@'192.25.10.73' identified by '123456';mysql> flush privileges;[root@localhost ~]#4.备份master端数据,并拷贝至slave端mysql> flush tables with read lock;query ok, 0 rows affected (0.00 sec)mysql> show master status;+------------------+----------+---------------+--------------------------------------------------+-------------------+| file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set |+------------------+----------+---------------+--------------------------------------------------+-------------------+| mysql-bin.000002 | 120 | db_kenyon | | |+------------------+----------+---------------+--------------------------------------------------+-------------------+1 row in set (0.00 sec)mysql> 另外一个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/ --回到之前的session,从机搭完以后解锁，保证数据一致mysql> unlock tables;query ok, 0 rows affected (0.00 sec)5.修改slave端的数据[mysqld]server-id = 2master-host = 192.25.10.71 #5.5以上废弃master-user = repl #5.5以上废弃master-password = 123456 #5.5以上废弃master-port = 3306 #5.5以上废弃master-connect-retry=60 #重试时间60秒, #5.5以上废弃replicate-do-db=db_kenyon #需要同步的库log-slave-updates=1 #启用从库日志，这样可以设置链式复制read-only = 1 #0表示可读写，1表示只读,但是只针对普通用户，超级用户和同步用户不受此限制6.重启slave，并指定主机检查mysql> change master to master_host='192.25.10.71',master_user='repl',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=120mysql> 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 read_master_log_pos: 120 relay_log_file: localhost-relay-bin.000001 relay_log_pos: 4 relay_master_log_file: mysql-bin.000002 slave_io_running: no slave_sql_running: no replicate_do_db: db_kenyon 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: 120 relay_log_space: 120 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: nullmaster_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: 0 master_uuid: master_info_file: /usr/local/mysql/data/master.info sql_delay: 0 sql_remaining_delay: null slave_sql_running_state: 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)
调整网络和mysql参数等，使两个参数：slave_io_running， slave_sql_running的状态为yes
7.级联过程
其他配置如上，注意级联参数log-slave-updates开启,该参数在my.cnf里值应为1，或者
mysql> show variables like 'log_slave_%';+-------------------+-------+| variable_name | value |+-------------------+-------+| log_slave_updates | on |+-------------------+-------+1 row in set (0.00 sec)
8.主主的过程就是主从的基础上再以从机为主机，反向操作一遍即可。简单测试，在71上建表，73、76上都能看到该表即成功。
四、级联的性能损耗测试
使用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
改变concurrency,得到如下结果:
并发数 平均时间s(后者开启级联) 每秒处理 损耗
2 5.324 1878 0.6s  11%
2 5.912 1691 0.6s  11%
10 2.521 3966 0.3s  12%
10 2.828 3536 0.3s  12%
50 2.161 4627 0.2s  7%
50 2.331 4290 0.2s  7%
100 2.355 4246 0.3s  11%
100 2.618 3819 0.3s  11%
bitscn.com