mysql-router是mysql官方的轻量级的中间件主要有如下工能：
功能 & 特性：
1、流量分发(只向一个实例导入流量、如果当前的实例宕机了、就会向集群的下一个实例导入流量)
2、一定的高可用能力
3、比之前的官方中间件mysql-proxy性能上有好一点
4、可以与mysql-fabric整合
1、下载
https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-2.0.4-linux-glibc2.12-x86-64bit.tar.gz
2、安装
tar -xzvf mysql-router-2.0.4-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/ln -s /usr/local/mysql-router-2.0.4-linux-glibc2.12-x86-64bit /usr/local/mysqlrouterexport /usr/local/mysqlrouter/bin/:$PATH
3、测试安装是否成功
mysqlrouter --helpMySQL Router v2.0.4 on Linux (64-bit) (GPL community edition)Copyright (c) 2015, 2016, 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.Start MySQL Router.Configuration read from the following files in the given order (enclosedin parentheses means not available for reading): (./mysqlrouter.ini) (/root/.mysqlrouter.ini)Usage: mysqlrouter [-v|--version] [-h|--help] [-c|--config=] [-a|--extra-config= ]Options: -v, --version Display version information and exit. -h, --help Display this help and exit. -c , --config Only read configuration from given file. -a , --extra-config Read this file after configuration files are read from either default locations or from files specified by the --config option.
4、配置mysql-router
/usr/local/mysqlrouter/etc/mysqlrouter.cnf
[DEFAULT]logging_folder =/usr/local/mysqlrouter/run/plugin_folder =/usr/local/mysqlrouter/lib/mysqlrouter/runtime_folder =/usr/local/mysqlrouter/run/config_folder =/usr/local/mysqlrouter/etc/[logger]level =debug[routing:7001]bind_address =0.0.0.0:7001connect_timeout =2mode =read-write# [read-write | read-only]max_connections =256max_connect_errors =1000client_connect_timeout =2destinations =127.0.0.1:3307,127.0.0.1:3308,127.0.0.1:3309
5、为了测试mysql-router的功能(读写分离+高可用)、于是初始化三个实例(3307,3308,3309)各个实例的配置文件如下
3307
[mysql]auto-rehash[mysqld]####: for globaluser =mysql # mysqlbasedir =/usr/local/mysql # /usr/local/mysql/datadir =/usr/local/datas/3307 # /usr/local/mysql/dataserver_id =1 # 0port =3307 # 3306character_set_server =utf8 # latin1log_timestamps =system # utcsocket =mysql.sock # /tmp/mysql.sockread_only =0 # offskip-slave-start =1 # auto_increment_increment =1 # 1auto_increment_offset =1 # 1lower_case_table_names =1 # 0secure_file_priv = # null####: for binlogbinlog_format =row # rowlog_bin =mysql-bin # offbinlog_rows_query_log_events =on # offlog_slave_updates =on # offexpire_logs_days =4 # 0binlog_cache_size =32768 # 32768(32k)binlog_checksum =none # CRC32sync_binlog =1 # 1####: for error-loglog_error =error.log # /usr/local/mysql/data/localhost.localdomain.err####: for slow query log####: for gtidgtid_executed_compression_period =1000 # 1000gtid_mode =on # offenforce_gtid_consistency =on # off####: for replicationmaster_info_repository =table # filerelay_log_info_repository =table # file####: for group replicationtransaction_write_set_extraction =XXHASH64 # offloose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # loose-group_replication_start_on_boot =off # offloose-group_replication_local_address ="127.0.0.1:24901" #loose-group_replication_group_seeds ="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group =off # off####: for innodbdefault_storage_engine =innodb # innodbdefault_tmp_storage_engine =innodb # innodbinnodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextendinnodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextendinnodb_buffer_pool_filename =ib_buffer_pool # ib_buffer_poolinnodb_log_group_home_dir =./ # ./innodb_log_files_in_group =2 # 2innodb_log_file_size =48M # 50331648(48M)innodb_file_format =Barracuda # Barracudainnodb_file_per_table =on # oninnodb_page_size =16k # 16384(16k)innodb_thread_concurrency =0 # 0innodb_read_io_threads =4 # 4innodb_write_io_threads =4 # 4innodb_purge_threads =4 # 4innodb_print_all_deadlocks =on # offinnodb_deadlock_detect =on # oninnodb_lock_wait_timeout =50 # 50innodb_spin_wait_delay =6 # 6innodb_autoinc_lock_mode =2 # 1innodb_stats_persistent =on # oninnodb_stats_persistent_sample_pages =20 # 20innodb_buffer_pool_instances =1 # 1innodb_adaptive_hash_index =on # oninnodb_change_buffering =all # allinnodb_change_buffer_max_size =25 # 25innodb_flush_neighbors =1 # 1innodb_flush_method =O_DIRECT # innodb_doublewrite =on # oninnodb_log_buffer_size =16M # 16777216(16M)innodb_flush_log_at_timeout =1 # 1innodb_flush_log_at_trx_commit =1 # 1innodb_buffer_pool_size =134217728 # 134217728(128M)autocommit =1 # 1#### for performance_schemaperformance_schema =on # onperformance_schema_consumer_events_stages_current =on # offperformance_schema_consumer_events_stages_history =on # offperformance_schema_consumer_events_stages_history_long =off # offperformance_schema_consumer_statements_digest =on # onperformance_schema_consumer_events_statements_current =on # onperformance_schema_consumer_events_statements_history =on # onperformance_schema_consumer_events_statements_history_long =off # offperformance_schema_consumer_events_waits_current =on # offperformance_schema_consumer_events_waits_history =on # offperformance_schema_consumer_events_waits_history_long =off # offperformance_schema_consumer_global_instrumentation =on # onperformance_schema_consumer_thread_instrumentation =on # on
3308
[mysql]auto-rehash[mysqld]####: for globaluser =mysql # mysqlbasedir =/usr/local/mysql # /usr/local/mysql/datadir =/usr/local/datas/3308 # /usr/local/mysql/dataserver_id =2 # 0port =3308 # 3306character_set_server =utf8 # latin1log_timestamps =system # utcsocket =mysql.sock # /tmp/mysql.sockread_only =0 # offskip-slave-start =1 # auto_increment_increment =1 # 1auto_increment_offset =1 # 1lower_case_table_names =1 # 0secure_file_priv = # null####: for binlogbinlog_format =row # rowlog_bin =mysql-bin # offbinlog_rows_query_log_events =on # offlog_slave_updates =on # offexpire_logs_days =4 # 0binlog_cache_size =32768 # 32768(32k)binlog_checksum =none # CRC32sync_binlog =1 # 1####: for error-loglog_error =error.log # /usr/local/mysql/data/localhost.localdomain.err####: for slow query log####: for gtidgtid_executed_compression_period =1000 # 1000gtid_mode =on # offenforce_gtid_consistency =on # off####: for replicationmaster_info_repository =table # filerelay_log_info_repository =table # file####: for group replicationtransaction_write_set_extraction =XXHASH64 # offloose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # loose-group_replication_start_on_boot =off # offloose-group_replication_local_address ="127.0.0.1:24901" #loose-group_replication_group_seeds ="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group =off # off####: for innodbdefault_storage_engine =innodb # innodbdefault_tmp_storage_engine =innodb # innodbinnodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextendinnodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextendinnodb_buffer_pool_filename =ib_buffer_pool # ib_buffer_poolinnodb_log_group_home_dir =./ # ./innodb_log_files_in_group =2 # 2innodb_log_file_size =48M # 50331648(48M)innodb_file_format =Barracuda # Barracudainnodb_file_per_table =on # oninnodb_page_size =16k # 16384(16k)innodb_thread_concurrency =0 # 0innodb_read_io_threads =4 # 4innodb_write_io_threads =4 # 4innodb_purge_threads =4 # 4innodb_print_all_deadlocks =on # offinnodb_deadlock_detect =on # oninnodb_lock_wait_timeout =50 # 50innodb_spin_wait_delay =6 # 6innodb_autoinc_lock_mode =2 # 1innodb_stats_persistent =on # oninnodb_stats_persistent_sample_pages =20 # 20innodb_buffer_pool_instances =1 # 1innodb_adaptive_hash_index =on # oninnodb_change_buffering =all # allinnodb_change_buffer_max_size =25 # 25innodb_flush_neighbors =1 # 1innodb_flush_method =O_DIRECT # innodb_doublewrite =on # oninnodb_log_buffer_size =16M # 16777216(16M)innodb_flush_log_at_timeout =1 # 1innodb_flush_log_at_trx_commit =1 # 1innodb_buffer_pool_size =134217728 # 134217728(128M)autocommit =1 # 1#### for performance_schemaperformance_schema =on # onperformance_schema_consumer_events_stages_current =on # offperformance_schema_consumer_events_stages_history =on # offperformance_schema_consumer_events_stages_history_long =off # offperformance_schema_consumer_statements_digest =on # onperformance_schema_consumer_events_statements_current =on # onperformance_schema_consumer_events_statements_history =on # onperformance_schema_consumer_events_statements_history_long =off # offperformance_schema_consumer_events_waits_current =on # offperformance_schema_consumer_events_waits_history =on # offperformance_schema_consumer_events_waits_history_long =off # offperformance_schema_consumer_global_instrumentation =on # onperformance_schema_consumer_thread_instrumentation =on # on
3309
[mysql]auto-rehash[mysqld]####: for globaluser =mysql # mysqlbasedir =/usr/local/mysql # /usr/local/mysql/datadir =/usr/local/datas/3309 # /usr/local/mysql/dataserver_id =3 # 0port =3309 # 3306character_set_server =utf8 # latin1log_timestamps =system # utcsocket =mysql.sock # /tmp/mysql.sockread_only =0 # offskip-slave-start =1 # auto_increment_increment =1 # 1auto_increment_offset =1 # 1lower_case_table_names =1 # 0secure_file_priv = # null####: for binlogbinlog_format =row # rowlog_bin =mysql-bin # offbinlog_rows_query_log_events =on # offlog_slave_updates =on # offexpire_logs_days =4 # 0binlog_cache_size =32768 # 32768(32k)binlog_checksum =none # CRC32sync_binlog =1 # 1####: for error-loglog_error =error.log # /usr/local/mysql/data/localhost.localdomain.err####: for slow query log####: for gtidgtid_executed_compression_period =1000 # 1000gtid_mode =on # offenforce_gtid_consistency =on # off####: for replicationmaster_info_repository =table # filerelay_log_info_repository =table # file####: for group replicationtransaction_write_set_extraction =XXHASH64 # offloose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" # loose-group_replication_start_on_boot =off # offloose-group_replication_local_address ="127.0.0.1:24901" #loose-group_replication_group_seeds ="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"loose-group_replication_bootstrap_group =off # off####: for innodbdefault_storage_engine =innodb # innodbdefault_tmp_storage_engine =innodb # innodbinnodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextendinnodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextendinnodb_buffer_pool_filename =ib_buffer_pool # ib_buffer_poolinnodb_log_group_home_dir =./ # ./innodb_log_files_in_group =2 # 2innodb_log_file_size =48M # 50331648(48M)innodb_file_format =Barracuda # Barracudainnodb_file_per_table =on # oninnodb_page_size =16k # 16384(16k)innodb_thread_concurrency =0 # 0innodb_read_io_threads =4 # 4innodb_write_io_threads =4 # 4innodb_purge_threads =4 # 4innodb_print_all_deadlocks =on # offinnodb_deadlock_detect =on # oninnodb_lock_wait_timeout =50 # 50innodb_spin_wait_delay =6 # 6innodb_autoinc_lock_mode =2 # 1innodb_stats_persistent =on # oninnodb_stats_persistent_sample_pages =20 # 20innodb_buffer_pool_instances =1 # 1innodb_adaptive_hash_index =on # oninnodb_change_buffering =all # allinnodb_change_buffer_max_size =25 # 25innodb_flush_neighbors =1 # 1innodb_flush_method =O_DIRECT # innodb_doublewrite =on # oninnodb_log_buffer_size =16M # 16777216(16M)innodb_flush_log_at_timeout =1 # 1innodb_flush_log_at_trx_commit =1 # 1innodb_buffer_pool_size =134217728 # 134217728(128M)autocommit =1 # 1#### for performance_schemaperformance_schema =on # onperformance_schema_consumer_events_stages_current =on # offperformance_schema_consumer_events_stages_history =on # offperformance_schema_consumer_events_stages_history_long =off # offperformance_schema_consumer_statements_digest =on # onperformance_schema_consumer_events_statements_current =on # onperformance_schema_consumer_events_statements_history =on # onperformance_schema_consumer_events_statements_history_long =off # offperformance_schema_consumer_events_waits_current =on # offperformance_schema_consumer_events_waits_history =on # offperformance_schema_consumer_events_waits_history_long =off # offperformance_schema_consumer_global_instrumentation =on # onperformance_schema_consumer_thread_instrumentation =on # on
6、分别启动这三个实例
mysqld --defaults-file=/usr/local/datas/my.cnf.3307 &mysqld --defaults-file=/usr/local/datas/my.cnf.3308 &mysqld --defaults-file=/usr/local/datas/my.cnf.3309 &
7、分别对这三个实例增加用户和库
3307
create user appuser@'%' identified by '123456';create user appuser@'127.0.0.1' identified by '123456';grant all on *.* to appuser@'%';grant all on *.* to appuser@'127.0.0.1';create database db3307;create database tempdb;create table tempdb.t(x int);insert into tempdb.t(x) values(3307);
3308
create user appuser@'%' identified by '123456';create user appuser@'127.0.0.1' identified by '123456';grant all on *.* to appuser@'%';grant all on *.* to appuser@'127.0.0.1';create database db3308;create database tempdb;create table tempdb.t(x int);insert into tempdb.t(x) values(3308);
3309
create user appuser@'%' identified by '123456';create user appuser@'127.0.0.1' identified by '123456';grant all on *.* to appuser@'%';grant all on *.* to appuser@'127.0.0.1';create database db3309;create database tempdb;create table tempdb.t(x int);insert into tempdb.t(x) values(3309);
8、启动mysqlrouter
mysqlrouter --config /usr/local/mysqlrouter/etc/mysqlrouter.cnf &
9、测试通过mysqlrouter这个中间件来连接mysql库
mysql -h127.0.0.1 -uappuser -p123456 -e"show databases;" -P7001mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database |+--------------------+| information_schema || db3307 || mysql || performance_schema || sys |+--------------------+
[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001mysql: [Warning] Using a password on the command line interface can be insecure.+------+| x |+------+| 3307 |+------+[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001mysql: [Warning] Using a password on the command line interface can be insecure.+------+| x |+------+| 3307 |+------+[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001mysql: [Warning] Using a password on the command line interface can be insecure.+------+| x |+------+| 3307 |+------+[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001mysql: [Warning] Using a password on the command line interface can be insecure.+------+| x |+------+| 3307 |+------+
由上面可以看出mysqlrouter总是把流量导向了3307这个实例好像没有看到什么读写分离的能力，难道只有高可用(只是一个分发器，一个mysql挂了，然后把
流量导到另一个上面去，如果是这样的话，还不如lvs呢！）
10、测试它的高可用能力(kill 掉3307)这个实例、然后再测试mysqlrouter把流量导到了哪里去
kill -9 pid_of_mysql_3307
mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001mysql: [Warning] Using a password on the command line interface can be insecure.+------+| x |+------+| 3308 |+------+[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001mysql: [Warning] Using a password on the command line interface can be insecure.+------+| x |+------+| 3308 |+------+[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001mysql: [Warning] Using a password on the command line interface can be insecure.+------+| x |+------+| 3308 |+------+[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001mysql: [Warning] Using a password on the command line interface can be insecure.+------+| x |+------+| 3308 |+------+
结论
由测试的结果可以看出mysqlrouter总是把流量导入到其配置文件中destinations项中最左边的那个活着的的实例上去；
建议后端的mysql集群使用mysql-group-replication来保存数据的一致性。
----