egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf[mysqld]datadir=/data1/mysqldbsocket=/tmp/mysql.sockkey_buffer_size   = 16Mmax_allowed_packet  = 16Mthread_stack   = 192Kthread_cache_size  = 8query_cache_limit  = 1Mquery_cache_size  = 64Mquery_cache_type  = 1symbolic-links=0innodb_file_per_table=ONskip_name_resolve=ONserver-id  = 1log_bin   = /data1/mysqldb/mysql-bin.log[mysqld_safe]log-error=/usr/local/mysql/logs/error.logpid-file=/data1/mysqldb/mysql.pid!includedir /usr/local/mysql/etc/my.cnf.d

mysql > grant replication client,replication slave on *.* to 'repluser'@'172.16.3.%' identified by 'replpass';mysql > flush privileges;mysql >show master logs;+------------------+-----------+| Log_name   | File_size |+------------------+-----------+| mysql-bin.000001 |  622 |

egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf[mysqld]datadir=/data1/mysqldbsocket=/data1/mysqldb/mysql.sockkey_buffer_size   = 16Mmax_allowed_packet  = 16Mthread_stack   = 192Kthread_cache_size  = 8query_cache_limit  = 1Mquery_cache_size  = 64Mquery_cache_type  = 1symbolic-links=0innodb_file_per_table=ONskip_name_resolve=ONserver-id    = 11   #從節(jié)點(diǎn)標(biāo)識(shí)ID 各從節(jié)點(diǎn)均不一樣 relay_log = relay-logread_only=ON[mysqld_safe]log-error=/usr/local/mysql/log/error.logpid-file=/var/run/mysql/mysql.pid!includedir /usr/local/mysql/etc/my.cnf.d

mysql > CHANGE MASTER TO MASTER_HOST="172.16.3.175",MASTER_USER="repluser",MASTER_PASSWORD="replpass",MASTER_PORT=3306,MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=622;mysql > start slave;  #啟動(dòng)從節(jié)點(diǎn)()#查看從節(jié)點(diǎn)狀態(tài)mysql > SHOW SLAVE STATUS;*************************** 1. row ***************************    Slave_IO_State: Waiting for master to send event     Master_Host: 172.16.3.175     #主節(jié)點(diǎn)     Master_User: repluser       #同步賬號(hào)     Master_Port: 3306    Connect_Retry: 60    Master_Log_File: mysql-bin.000001   Read_Master_Log_Pos: 622    Relay_Log_File: relay-log.000001    Relay_Log_Pos: 582  Relay_Master_Log_File: mysql-bin.000001    Slave_IO_Running: Yes      #同步線程正常   Slave_SQL_Running: Yes     #本地寫(xiě)線程正常    Replicate_Do_DB:        #同步過(guò)濾為空(可以只同步某個(gè)或某些庫(kù))   Replicate_Ignore_DB:        #不同步的庫(kù)   Replicate_Do_Table:        Replicate_Ignore_Table:   Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:      Last_Errno: 0    #最后同步的錯(cuò)誤 0表示正常同步     Last_Error:      Skip_Counter: 0   Exec_Master_Log_Pos: 622    Relay_Log_Space: 615    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: 0Master_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: 1     Master_UUID: 57017c43-36e3-11e8-ac76-080027393fc7    Master_Info_File: /data1/mysqldb/master.info     SQL_Delay: 0   SQL_Remaining_Delay: NULL  Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates   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: 0   Replicate_Rewrite_DB:      Channel_Name:    Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified

[root@db1 ~]# mysql < Testdb.sql登錄數(shù)據(jù)庫(kù)[root@db1 ~]# mysql -uroot -pmysql> show databases;+--------------------+| Database   |+--------------------+ study    |+--------------------+5 rows in set (0.00 sec)###study測(cè)試數(shù)據(jù)庫(kù)導(dǎo)入成功mysql> use study;Database changedmysql> show tables;+-----------------+| Tables_in_study |+-----------------+| class   || course   || part   || score   || student   || tb31   || tb32   || teacher   || test1   || test2   || user_info  |+-----------------+11 rows in set (0.00 sec)#刪除test1 test2表

mysql> show tables;+-----------------+| Tables_in_study |+-----------------+| class   || course   || part   || score   || student   || tb31   || tb32   || teacher   || user_info  |+-----------------+9 rows in set (0.00 sec)

[root@proxysql ~]# yum install proxysql-1.3.6-1-centos7.x86_64.rpm -y[root@proxysql ~]# rpm -ql proxysql/etc/init.d/proxysql/etc/proxysql.cnf  #主配置文件/usr/bin/proxysql/usr/share/proxysql/tools/proxysql_galera_checker.sh/usr/share/proxysql/tools/proxysql_galera_writer.pl

[root@proxysql ~]# egrep -v '(^$|^#)' /etc/proxysql.cnfdatadir="/var/lib/proxysql"admin_variables={  admin_credentials="admin:admin"    #proxysql自己的管理用戶(hù)名密碼 mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"}mysql_variables={ threads=4       #線程數(shù),建議和cpu核心數(shù)一致 max_connections=2048  #最大連接 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:3306;/tmp/proxysql.sock"    #對(duì)外接口 default_schema="information_schema" stacksize=1048576 server_version="5.5.30" connect_timeout_server=3000 monitor_username="monitor" monitor_password="monitor" monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10}#####主從節(jié)點(diǎn)的配置mysql_servers =      ( {  address = "172.16.3.175" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain  port = 3306   # no default, required . If port is 0 , address is interpred as a Unix Socket Domain  hostgroup = 1   # 設(shè)置組號(hào)  status = "ONLINE"  # default: ONLINE  weight = 1   # default: 1  compression = 0  # default: 0  max_connections = 200   ###定義最大的連接 }, {  address = "172.16.3.235" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain  port = 3306   # no default, required . If port is 0 , address is interpred as a Unix Socket Domain  hostgroup = 2   # no default, required  status = "ONLINE"  # default: ONLINE  weight = 1   # default: 1  compression = 0  # default: 0  max_connections=1000 }, {  address = "172.16.3.241" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain  port = 3306   # no default, required . If port is 0 , address is interpred as a Unix Socket Domain  hostgroup = 2   # no default, required  status = "ONLINE"  # default: ONLINE  weight = 1   # default: 1  compression = 0  # default: 0  max_connections=1000 })mysql_users:( {  username = "myadmin" # no default , required  password = "mypass" # default: ''  default_hostgroup = 1 # default: 0  max_connections=1000  default_schema="test"  active = 1   #是否激活 })mysql_query_rules:()scheduler=()mysql_replication_hostgroups=(  {    writer_hostgroup=1   #定義寫(xiě)組號(hào)1    reader_hostgroup=2  #定義讀組號(hào)2    comment="test repl 1" #注釋內(nèi)容  })

模擬通過(guò)proxysql使用數(shù)據(jù)庫(kù)[root@proxysql ]# mysql -h172.16.3.175 -umyadmin -pmypassmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or /g.Your MySQL connection id is 17406Server version: 5.7.21-log MySQL Community Server (GPL)Copyright (c) 2000, 2018, 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> mysql > show databases;mysql> show databases;+--------------------+| Database   |+--------------------+| information_schema || mysql    || performance_schema || study    || sys    |+--------------------+5 rows in set (0.00 sec)###刪除study數(shù)據(jù)庫(kù)user_info中6 -12之間的數(shù)據(jù)刪除之前:mysql> select * from user_info;+-----+-------+------+--------+----------+| nid | name | age | gender | part_nid |+-----+-------+------+--------+----------+| 1 | san | 20 | 男  |  1 || 2 | dong | 29 | 男  |  2 || 4 | Ling | 28 | 男  |  4 || 5 | ling | 28 | 男  |  3 || 6 | dong | 30 | 男  |  1 || 7 | b  | 11 | 女  |  1 || 8 | c  | 12 | 女  |  1 || 9 | d  | 18 | 女  |  4 || 10 | e  | 22 | 男  |  3 || 11 | f  | 23 | 男  |  2 || 12 | dongy | 22 | 男  |  1 |+-----+-------+------+--------+----------+11 rows in set (0.00 sec)刪除之后:mysql> delete from user_info where nid >6 and nid <12;Query OK, 5 rows affected (0.03 sec)mysql> select * from user_info;+-----+-------+------+--------+----------+| nid | name | age | gender | part_nid |+-----+-------+------+--------+----------+| 1 | san | 20 | 男  |  1 || 2 | dong | 29 | 男  |  2 || 4 | Ling | 28 | 男  |  4 || 5 | ling | 28 | 男  |  3 || 6 | dong | 30 | 男  |  1 || 12 | dongy | 22 | 男  |  1 |+-----+-------+------+--------+----------+6 rows in set (0.00 sec)

主節(jié)點(diǎn):類(lèi)似如下:[root@db1 ~]# tcpdump -i enp0s3 -nn tcp port 3306tcpdump: verbose output suppressed, use -v or -vv for full protocol decodelistening on enp0s3, link-type EN10MB (Ethernet), capture size 262144 bytes18:04:34.678861 IP 172.16.3.254.42191 > 172.16.3.175.3306: Flags [S], seq 3385407732, win 29200, options [mss 1460,sackOK,TS val 17576713 ecr 0,nop,wscale 7], length 018:04:34.678908 IP 172.16.3.175.3306 > 172.16.3.254.42191: Flags [S.], seq 1579426335, ack 3385407733, win 28960, options [mss 1460,sackOK,TS val 29413673 ecr 17576713,nop,wscale 7], length 018:04:34.680902 IP 172.16.3.254.42191 > 172.16.3.175.3306: Flags [.], ack 1, win 229, options [nop,nop,TS val 17576715 ecr 29413673], length 018:04:34.681264 IP 172.16.3.175.3306 > 172.16.3.254.42191: Flags [P.], seq 1:83, ack 1, win 227, options [nop,nop,TS val 29413675 ecr 17576715], length 82....從節(jié)點(diǎn):類(lèi)似如下:[root@db2 data1]# tcpdump -i enp0s3 -nn tcp port 3306tcpdump: verbose output suppressed, use -v or -vv for full protocol decodelistening on enp0s3, link-type EN10MB (Ethernet), capture size 262144 bytes18:02:57.932043 IP 172.16.3.254.42733 > 172.16.3.235.3306: Flags [S], seq 76520456, win 29200, options [mss 1460,sackOK,TS val 17479189 ecr 0,nop,wscale 7], length 0...........

 [root@proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> 'Admin> show databases;+-----+---------+-------------------------------+| seq | name | file       |+-----+---------+-------------------------------+| 0 | main |        || 2 | disk | /var/lib/proxysql/proxysql.db || 3 | stats |        || 4 | monitor |        |+-----+---------+-------------------------------+4 rows in set (0.00 sec)