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/mysqlrouter
export/usr/local/mysqlrouter/bin/:$PATH
3、测试安装是否成功
mysqlrouter --help
MySQL 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 its
affiliates. Other names may be trademarks of their respective
owners.
Start MySQL Router.
Configuration read from the following filesinthe given order (enclosedin parentheses means not available forreading):
(./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 givenfile.-a , --extra-config Read thisfileafter 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 global
user =mysql #mysql
basedir =/usr/local/mysql #/usr/local/mysql/
datadir =/usr/local/datas/3307 #/usr/local/mysql/data
server_id =1 #0
port =3307 #3306
character_set_server =utf8 #latin1
log_timestamps =system #utc
socket =mysql.sock #/tmp/mysql.sock
read_only =0 #off
skip-slave-start =1 #
auto_increment_increment =1 #1
auto_increment_offset =1 #1
lower_case_table_names =1 #0
secure_file_priv = #null
####: for binlog
binlog_format =row #row
log_bin =mysql-bin #off
binlog_rows_query_log_events =on #off
log_slave_updates =on #off
expire_logs_days =4 #0
binlog_cache_size =32768 #32768(32k)
binlog_checksum =none #CRC32
sync_binlog =1 #1
####: for error-log
log_error =error.log #/usr/local/mysql/data/localhost.localdomain.err
####: for slow query log
####: for gtid
gtid_executed_compression_period =1000 #1000
gtid_mode =on #off
enforce_gtid_consistency =on #off
####: for replication
master_info_repository =table #file
relay_log_info_repository =table #file
####: for group replication
transaction_write_set_extraction =XXHASH64 #off
loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #
loose-group_replication_start_on_boot =off #off
loose-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 innodb
default_storage_engine =innodb #innodb
default_tmp_storage_engine =innodb #innodb
innodb_data_file_path =ibdata1:12M:autoextend #ibdata1:12M:autoextend
innodb_temp_data_file_path =ibtmp1:12M:autoextend #ibtmp1:12M:autoextend
innodb_buffer_pool_filename =ib_buffer_pool #ib_buffer_pool
innodb_log_group_home_dir =./ #./
innodb_log_files_in_group =2 #2
innodb_log_file_size =48M #50331648(48M)
innodb_file_format =Barracuda #Barracuda
innodb_file_per_table =on #on
innodb_page_size =16k #16384(16k)
innodb_thread_concurrency =0 #0
innodb_read_io_threads =4 #4
innodb_write_io_threads =4 #4
innodb_purge_threads =4 #4
innodb_print_all_deadlocks =on #off
innodb_deadlock_detect =on #on
innodb_lock_wait_timeout =50 #50
innodb_spin_wait_delay =6 #6
innodb_autoinc_lock_mode =2 #1
innodb_stats_persistent =on #on
innodb_stats_persistent_sample_pages =20 #20
innodb_buffer_pool_instances =1 #1
innodb_adaptive_hash_index =on #on
innodb_change_buffering =all #all
innodb_change_buffer_max_size =25 #25
innodb_flush_neighbors =1 #1
innodb_flush_method =O_DIRECT #
innodb_doublewrite =on #on
innodb_log_buffer_size =16M #16777216(16M)
innodb_flush_log_at_timeout =1 #1
innodb_flush_log_at_trx_commit =1 #1
innodb_buffer_pool_size =134217728 #134217728(128M)
autocommit =1 #1
#### for performance_schema
performance_schema =on #on
performance_schema_consumer_events_stages_current =on #off
performance_schema_consumer_events_stages_history =on #off
performance_schema_consumer_events_stages_history_long =off #off
performance_schema_consumer_statements_digest =on #on
performance_schema_consumer_events_statements_current =on #on
performance_schema_consumer_events_statements_history =on #on
performance_schema_consumer_events_statements_history_long =off #off
performance_schema_consumer_events_waits_current =on #off
performance_schema_consumer_events_waits_history =on #off
performance_schema_consumer_events_waits_history_long =off #off
performance_schema_consumer_global_instrumentation =on #on
performance_schema_consumer_thread_instrumentation =on #on
View Code
3308
[mysql]
auto-rehash
[mysqld]####: for global
user =mysql #mysql
basedir =/usr/local/mysql #/usr/local/mysql/
datadir =/usr/local/datas/3308 #/usr/local/mysql/data
server_id =2 #0
port =3308 #3306
character_set_server =utf8 #latin1
log_timestamps =system #utc
socket =mysql.sock #/tmp/mysql.sock
read_only =0 #off
skip-slave-start =1 #
auto_increment_increment =1 #1
auto_increment_offset =1 #1
lower_case_table_names =1 #0
secure_file_priv = #null
####: for binlog
binlog_format =row #row
log_bin =mysql-bin #off
binlog_rows_query_log_events =on #off
log_slave_updates =on #off
expire_logs_days =4 #0
binlog_cache_size =32768 #32768(32k)
binlog_checksum =none #CRC32
sync_binlog =1 #1
####: for error-log
log_error =error.log #/usr/local/mysql/data/localhost.localdomain.err
####: for slow query log
####: for gtid
gtid_executed_compression_period =1000 #1000
gtid_mode =on #off
enforce_gtid_consistency =on #off
####: for replication
master_info_repository =table #file
relay_log_info_repository =table #file
####: for group replication
transaction_write_set_extraction =XXHASH64 #off
loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #
loose-group_replication_start_on_boot =off #off
loose-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 innodb
default_storage_engine =innodb #innodb
default_tmp_storage_engine =innodb #innodb
innodb_data_file_path =ibdata1:12M:autoextend #ibdata1:12M:autoextend
innodb_temp_data_file_path =ibtmp1:12M:autoextend #ibtmp1:12M:autoextend
innodb_buffer_pool_filename =ib_buffer_pool #ib_buffer_pool
innodb_log_group_home_dir =./ #./
innodb_log_files_in_group =2 #2
innodb_log_file_size =48M #50331648(48M)
innodb_file_format =Barracuda #Barracuda
innodb_file_per_table =on #on
innodb_page_size =16k #16384(16k)
innodb_thread_concurrency =0 #0
innodb_read_io_threads =4 #4
innodb_write_io_threads =4 #4
innodb_purge_threads =4 #4
innodb_print_all_deadlocks =on #off
innodb_deadlock_detect =on #on
innodb_lock_wait_timeout =50 #50
innodb_spin_wait_delay =6 #6
innodb_autoinc_lock_mode =2 #1
innodb_stats_persistent =on #on
innodb_stats_persistent_sample_pages =20 #20
innodb_buffer_pool_instances =1 #1
innodb_adaptive_hash_index =on #on
innodb_change_buffering =all #all
innodb_change_buffer_max_size =25 #25
innodb_flush_neighbors =1 #1
innodb_flush_method =O_DIRECT #
innodb_doublewrite =on #on
innodb_log_buffer_size =16M #16777216(16M)
innodb_flush_log_at_timeout =1 #1
innodb_flush_log_at_trx_commit =1 #1
innodb_buffer_pool_size =134217728 #134217728(128M)
autocommit =1 #1
#### for performance_schema
performance_schema =on #on
performance_schema_consumer_events_stages_current =on #off
performance_schema_consumer_events_stages_history =on #off
performance_schema_consumer_events_stages_history_long =off #off
performance_schema_consumer_statements_digest =on #on
performance_schema_consumer_events_statements_current =on #on
performance_schema_consumer_events_statements_history =on #on
performance_schema_consumer_events_statements_history_long =off #off
performance_schema_consumer_events_waits_current =on #off
performance_schema_consumer_events_waits_history =on #off
performance_schema_consumer_events_waits_history_long =off #off
performance_schema_consumer_global_instrumentation =on #on
performance_schema_consumer_thread_instrumentation =on #on
View Code
3309
[mysql]
auto-rehash
[mysqld]####: for global
user =mysql #mysql
basedir =/usr/local/mysql #/usr/local/mysql/
datadir =/usr/local/datas/3309 #/usr/local/mysql/data
server_id =3 #0
port =3309 #3306
character_set_server =utf8 #latin1
log_timestamps =system #utc
socket =mysql.sock #/tmp/mysql.sock
read_only =0 #off
skip-slave-start =1 #
auto_increment_increment =1 #1
auto_increment_offset =1 #1
lower_case_table_names =1 #0
secure_file_priv = #null
####: for binlog
binlog_format =row #row
log_bin =mysql-bin #off
binlog_rows_query_log_events =on #off
log_slave_updates =on #off
expire_logs_days =4 #0
binlog_cache_size =32768 #32768(32k)
binlog_checksum =none #CRC32
sync_binlog =1 #1
####: for error-log
log_error =error.log #/usr/local/mysql/data/localhost.localdomain.err
####: for slow query log
####: for gtid
gtid_executed_compression_period =1000 #1000
gtid_mode =on #off
enforce_gtid_consistency =on #off
####: for replication
master_info_repository =table #file
relay_log_info_repository =table #file
####: for group replication
transaction_write_set_extraction =XXHASH64 #off
loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #
loose-group_replication_start_on_boot =off #off
loose-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 innodb
default_storage_engine =innodb #innodb
default_tmp_storage_engine =innodb #innodb
innodb_data_file_path =ibdata1:12M:autoextend #ibdata1:12M:autoextend
innodb_temp_data_file_path =ibtmp1:12M:autoextend #ibtmp1:12M:autoextend
innodb_buffer_pool_filename =ib_buffer_pool #ib_buffer_pool
innodb_log_group_home_dir =./ #./
innodb_log_files_in_group =2 #2
innodb_log_file_size =48M #50331648(48M)
innodb_file_format =Barracuda #Barracuda
innodb_file_per_table =on #on
innodb_page_size =16k #16384(16k)
innodb_thread_concurrency =0 #0
innodb_read_io_threads =4 #4
innodb_write_io_threads =4 #4
innodb_purge_threads =4 #4
innodb_print_all_deadlocks =on #off
innodb_deadlock_detect =on #on
innodb_lock_wait_timeout =50 #50
innodb_spin_wait_delay =6 #6
innodb_autoinc_lock_mode =2 #1
innodb_stats_persistent =on #on
innodb_stats_persistent_sample_pages =20 #20
innodb_buffer_pool_instances =1 #1
innodb_adaptive_hash_index =on #on
innodb_change_buffering =all #all
innodb_change_buffer_max_size =25 #25
innodb_flush_neighbors =1 #1
innodb_flush_method =O_DIRECT #
innodb_doublewrite =on #on
innodb_log_buffer_size =16M #16777216(16M)
innodb_flush_log_at_timeout =1 #1
innodb_flush_log_at_trx_commit =1 #1
innodb_buffer_pool_size =134217728 #134217728(128M)
autocommit =1 #1
#### for performance_schema
performance_schema =on #on
performance_schema_consumer_events_stages_current =on #off
performance_schema_consumer_events_stages_history =on #off
performance_schema_consumer_events_stages_history_long =off #off
performance_schema_consumer_statements_digest =on #on
performance_schema_consumer_events_statements_current =on #on
performance_schema_consumer_events_statements_history =on #on
performance_schema_consumer_events_statements_history_long =off #off
performance_schema_consumer_events_waits_current =on #off
performance_schema_consumer_events_waits_history =on #off
performance_schema_consumer_events_waits_history_long =off #off
performance_schema_consumer_global_instrumentation =on #on
performance_schema_consumer_thread_instrumentation =on #on
View Code
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 databasedb3307;create databasetempdb;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 databasedb3308;create databasetempdb;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 databasedb3309;create databasetempdb;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;" -P7001
mysql:[Warning] Using a password onthe 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;" -P7001
mysql:[Warning] Using a password onthe command line interface can be insecure.+------+
| x |
+------+
| 3307 |
+------+
[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001
mysql:[Warning] Using a password onthe command line interface can be insecure.+------+
| x |
+------+
| 3307 |
+------+
[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001
mysql:[Warning] Using a password onthe command line interface can be insecure.+------+
| x |
+------+
| 3307 |
+------+
[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001
mysql:[Warning] Using a password onthe 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;" -P7001
mysql:[Warning] Using a password onthe command line interface can be insecure.+------+
| x |
+------+
| 3308 |
+------+
[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001
mysql:[Warning] Using a password onthe command line interface can be insecure.+------+
| x |
+------+
| 3308 |
+------+
[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001
mysql:[Warning] Using a password onthe command line interface can be insecure.+------+
| x |
+------+
| 3308 |
+------+
[jianglexing@workspace Desktop]$ mysql -h127.0.0.1 -uappuser -p123456 -e"select * from tempdb.t;" -P7001
mysql:[Warning] Using a password onthe command line interface can be insecure.+------+
| x |
+------+
| 3308 |
+------+
结论
由测试的结果可以看出mysqlrouter总是把流量导入到其配置文件中destinations项中最左边的那个活着的的实例上去；
建议后端的mysql集群使用mysql-group-replication来保存数据的一致性。
----
4007
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
