爱站程序员基地1 案例1：实现MySQL读写分离
1.1 问题
• 搭建一主一从结构
• 配置maxscale代理服务器
• 测试分离配置
1.2 方案
使用4台RHEL 7虚拟机，如图-1所示。其中192.168.4.10和192.168.4.20，分别提供读、写服务，均衡流量，通过主从复制保持数据一致性，由MySQL代理192.168.4.100面向客户端，收到SQL写请求时，交给服务器A处理，收到SQL读请求时，交给服务器B处理。linux客户机用于测试配置，可以使用真机代替
图－1
1.3 步骤
实现此案例需要按照如下步骤进行。
步骤一：搭建主从
1）搭建一主一从结构，主库192.168.4.10上面操作
1. [root@zlz ~]# vim /etc/my.cnf2. [mysqld]3. server_id=10 //指定服务器ID号4. log-bin=master10 //启用binlog日志，并指定文件名前缀5. ...6. [root@zlz ~]# systemctl restart mysqld //重启mysqld
2）从库192.168.4.20上面操作
1. [mysqld]2. server_id=20 //指定服务器ID号，不要与Master的相同3. log-bin=slave20 //启动SQL日志，并指定文件名前缀4. read_only=1 //只读模式5. ...6. [root@zlz ~]# systemctl restart mysqld
3）主库授权一个用户并查看master的状态
1. [root@zlz ~]# mysql -u root -p1234562. mysql> grant all on *.* to \'replicater\'@\'%\' identified by \'123456\';3. Query OK, 0 rows affected, 1 warning (0.00 sec)4. mysql> show master status;5. +-----------------+----------+--------------+------------------+-------------------+6. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |7. +-----------------+----------+--------------+------------------+-------------------+8. | master10.000002 | 449 | | | |9. +-----------------+----------+--------------+------------------+-------------------+10. 1 row in set (0.00 sec)
4）从库通过CHANGE MASTER语句指定MASTER服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置（参考MASTER上的状态输出）
1. [root@zlz ~]# mysql -u root -p1234562. mysql> change master to master_host=\'192.168.4.10\',3. -> master_user=\'replicater\',4. -> master_password=\'123456\',5. -> master_log_file=\'master10.000002\',6. -> master_log_pos=738;7. Query OK, 0 rows affected, 2 warnings (0.01 sec)8.9. mysql> start slave;10. Query OK, 0 rows affected (0.01 sec)11.12. mysql> show slave status\\G;13. *************************** 1. row ***************************14. Slave_IO_State: Waiting for master to send event15. Master_Host: 192.168.4.1016. Master_User: replicater17. Master_Port: 330618. Connect_Retry: 6019. Master_Log_File: master10.00000220. Read_Master_Log_Pos: 73821. Relay_Log_File: slave20-relay-bin.00000222. Relay_Log_Pos: 31923. Relay_Master_Log_File: master10.00000224. Slave_IO_Running: Yes25. Slave_SQL_Running: Yes26. Replicate_Do_DB:27. Replicate_Ignore_DB:28. Replicate_Do_Table:29. Replicate_Ignore_Table:30. Replicate_Wild_Do_Table:31. Replicate_Wild_Ignore_Table:32. Last_Errno: 033. Last_Error:34. Skip_Counter: 035. Exec_Master_Log_Pos: 73836. Relay_Log_Space: 52837. Until_Condition: None38. Until_Log_File:39. Until_Log_Pos: 040. Master_SSL_Allowed: No41. Master_SSL_CA_File:42. Master_SSL_CA_Path:43. Master_SSL_Cert:44. Master_SSL_Cipher:45. Master_SSL_Key:46. Seconds_Behind_Master: 047. Master_SSL_Verify_Server_Cert: No48. Last_IO_Errno: 049. Last_IO_Error:50. Last_SQL_Errno: 051. Last_SQL_Error:52. Replicate_Ignore_Server_Ids:53. Master_Server_Id: 1054. Master_UUID: 95ada2c2-bb24-11e8-abdb-525400131c0f55. Master_Info_File: /var/lib/mysql/master.info56. SQL_Delay: 057. SQL_Remaining_Delay: NULL58. Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates59. Master_Retry_Count: 8640060. Master_Bind:61. Last_IO_Error_Timestamp:62. Last_SQL_Error_Timestamp:63. Master_SSL_Crl:64. Master_SSL_Crlpath:65. Retrieved_Gtid_Set:66. Executed_Gtid_Set:67. Auto_Position: 068. Replicate_Rewrite_DB:69. Channel_Name:70. Master_TLS_Version:71. 1 row in set (0.00 sec)
5）测试，主库创建aa库
1. mysql> create database aa;2. Query OK, 1 row affected (0.00 sec)3.4. mysql> show databases;5. +--------------------+6. | Database |7. +--------------------+8. | information_schema |9. | aa |10. | mysql |11. | performance_schema |12. | sys |13. +--------------------+14. 5 rows in set (0.00 sec)
6）从库上面查看，有aa库
1. mysql> show databases;2.3. +--------------------+4. | Database |5. +--------------------+6. | information_schema |7. | aa |8. | mysql |9. | performance_schema |10. | sys |11. +--------------------+12. 5 rows in set (0.00 sec)
步骤二：实现mysql读写分离
1）配置数据读写分离服务器192.168.4.100
环境准备关闭防火墙和SElinux，保证yum源可以正常使用
1. [root@zlz ~]# cd mysql/2. [root@zlz mysql]# ls3. maxscale-2.1.2-1.rhel.7.x86_64.rpm4. [root@zlz mysql]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm5. //安装maxscale6. warning: maxscale-2.1.2-1.rhel.7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 8167ee24: NOKEY7. Preparing... ################################# [100%]8. Updating / installing...9. 1:maxscale-2.1.2-1 ################################# [100%]
2）配置maxscale
1. [root@zlz mysql]# vim /etc/maxscale.cnf.template2. [maxscale]3. threads=auto //运行的线程的数量4.5. [server1] //定义数据库服务器6. type=server7. address=192.168.4.10 //数据库服务器的ip8. port=33069. protocol=MySQLBackend //后端数据库10.11.12.13. [server2]14. type=server15. address=192.168.4.2016. port=330617. protocol=MySQLBackend18.19.20.21. [MySQL Monitor] //定义监控的数据库服务器22. type=monitor23. module=mysqlmon24. servers=server1, server2 //监控的数据库列表，不能写ip25. user=scalemon //监视数据库服务器时连接的用户名scalemon26. passwd=123456 //密码12345627. monitor_interval=10000 //监视的频率 单位为秒28.29.30.31. #[Read-Only Service] //不定义只读服务器32. #type=service33. #router=readconnroute34. #servers=server135. #user=myuser36. #passwd=mypwd37. #router_options=slave38.39.40.41. [Read-Write Service] //定义读写分离服务42. type=service43. router=readwritesplit44. servers=server1, server245. user=maxscaled //用户名 验证连接代理服务时访问数据库服务器的用户是否存在46. passwd=123456 //密码47. max_slave_connections=100%48.49.50.51. [MaxAdmin Service] //定义管理服务52. type=service53. router=cli54.55.56.57. #[Read-Only Listener] //不定义只读服务使用的端口号58. #type=listener59. #service=Read-Only Service60. #protocol=MySQLClient61. #port=400862.63.64.65. [Read-Write Listener] //定义读写服务使用的端口号66. type=listener67. service=Read-Write Service68. protocol=MySQLClient69. port=400670.71.72.73. [MaxAdmin Listener] //管理服务使用的端口号74. type=listener75. service=MaxAdmin Service76. protocol=maxscaled77. socket=default78. port=4099 //手动添加，不指定时使用的是默认端口在启动服务以后可以知道默认端口是多少
3）根据配置文件的设置，在数据库服务器上添加授权用户（主库执行，从库查看）
1. mysql> grant replication slave,replication client on *.* to scalemon@\'%\' identified by \"123456\"; //监控数据库服务器时，连接数据库服务器的用户2. Query OK, 0 rows affected, 1 warning (0.00 sec)3.4. mysql> grant select on mysql.* to maxscaled@\"%\" identified by \"123456\";5. //验证 访问数据时，连接数据库服务器使用的用户，是否在数据库服务器上存在的，连接用户6. Query OK, 0 rows affected, 1 warning (0.01 sec)
4）查看授权用户
在主库上面查看
1. mysql> select user,host from mysql.user where user in (\"scalemon\",\"maxscaled\");2. +-----------+------+3. | user | host |4. +-----------+------+5. | maxscaled | % |6. | scalemon | % |7. +-----------+------+8. 2 rows in set (0.00 sec)
在从库上面查看
1. mysql> select user,host from mysql.user where user in (\"scalemon\",\"maxscaled\");2. +-----------+------+3. | user | host |4. +-----------+------+5. | maxscaled | % |6. | scalemon | % |7. +-----------+------+8. 2 rows in set (0.00 sec)
测试授权用户
1. [root@zlz mysql]# mysql -h 192.168.4.10 -u scalemon -p1234562. [root@zlz mysql]# mysql -h 192.168.4.20 -u scalemon -p1234563. [root@zlz mysql]# mysql -h 192.168.4.10 -u maxscaled -p1234564. [root@zlz mysql]# mysql -h 192.168.4.20 -u maxscaled -p123456
5）启动服务
1. [root@zlz ~]# maxscale -f /etc/maxscale.cnf2. [root@zlz ~]# ps -C maxscale //查看进程3. PID TTY TIME CMD4. 17930 ? 00:00:00 maxscale5. [root@zlz ~]# netstat -antup | grep maxscale //查看端口6. tcp6 0 0 :::4099 :::* LISTEN 17930/maxscale7. tcp6 0 0 :::4006 :::* LISTEN 17930/maxscale
6）测试，在本机访问管理端口查看监控状态
maxadmin -P端口 -u用户名 -p密码
1. [root@zlz ~]# maxadmin -P4099 -uadmin -pmariadb2. MaxScale>3. MaxScale> list servers4. Servers.5. -------------------+-----------------+-------+-------------+--------------------6. Server | Address | Port | Connections | Status7. -------------------+-----------------+-------+-------------+--------------------8. server1 | 192.168.4.10 | 3306 | 0 | Master, Running9. server2 | 192.168.4.20 | 3306 | 0 | Slave, Running10. -------------------+-----------------+-------+-------------+--------------------
7）在客户端访问读写分离服务器（没有mysql命令可以安装）
mysql -h读写分离服务ip -P4006 -u用户名 -p密码
1. [root@zlz ~]# mysql -h192.168.4.100 -P4006 -ureplicater -p1234562. mysql> select @@hostname; //查看当前主机名3. +------------+4. | @@hostname |5. +------------+6. | slave20 |7. +------------+8. 1 row in set (0.00 sec)9. mysql> create table t2(id int(4) );10. Query OK, 0 rows affected (0.02 sec)11.12. mysql> insert into aa.t2 values(777);13. Query OK, 1 row affected (0.01 sec)
在主库上面查看
1. mysql> use aa2. mysql> select * from t2;3. +------+4. | id |5. +------+6. | 777 |7. +------+8. 1 row in set (0.00 sec)
从库（主库同步到从库）
1. mysql> use aa2. mysql> select * from t2;3. +------+4. | id |5. +------+6. | 777 |7. +------+8. 1 row in set (0.00 sec)
2 案例2：配置MySQL多实例
2.1 问题
• 在主机192.168.4.56上，配置第1个MySQL实例
• 实例名称mysql1、端口3307
• 数据库目录/data3307、pid文件mysql1.pid
• 错误日志mysql1.err
• 在主机192.168.4.56上，配置第2个MySQL实例
• 实例名称mysql2、端口3308
• 数据库目录/data3308、pid文件mysql2.pid
• 错误日志mysql2.err
步骤一：配置多实例（192.168.4.56上面操作）
什么是多实例：
在一台物理主机上运行多个数据库服务，可以节约运维成本，提高硬件利用率
1）解压软件、修改目录名
1. [root@zlz ~]# cd mysql/2. [root@zlz mysql]# ls3. mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz4. [root@zlz mysql]# tar -xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz5. [root@zlz mysql]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
2）调整PATH变量
1. [root@zlz mysql]# echo \"export PATH=/usr/local/mysql/bin:$PATH\" \\2. >> /etc/profile3. [root@zlz mysql]# source /etc/profile4. [root@zlz mysql]# echo $PATH5. /usr/local/mysql/bin:/usr/local/mycat/bin:/usr/local/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin
3）编辑主配置文件/etc/my.cnf
每个实例要有独立的数据库目录、监听端口号、实例名称和独立的sock文件
1. [mysqld_multi] //启用多实例2. mysqld = /usr/local/mysql/bin/mysqld_safe //指定进程文件路径3. mysqladmin = /usr/local/mysql/bin/mysqladmin //指定管理命令路径4. user = root //指定进程用户5.6. [mysqld1] //实例进程名称7. port=3307 //端口号8. datadir=/data3307 //数据库目录 ，要手动创建9. socket=/data3307/mysqld.sock //指定sock文件的路径和名称10. pid-file=/data3307/mysql1.pid //进程pid号文件位置11. log-error=/data3307/mysql1.err //错误日志位置12.13. [mysqld2]14. port=330815. datadir=/data330816. socket=/data3308/mysqld.sock17. pid-file=/data3308/mysql2.pid18. log-error=/data3308/mysql2.err
4）创建数据库目录
1. [root@zlz mysql]# mkdir -p /data33072. [root@zlz mysql]# mkdir -p /data3308
5）创建进程运行的所有者和组 mysql
1. [root@zlz mysql]# useradd mysql2. [root@zlz mysql]# chown mysql:mysql /data*
6）初始化授权库
1. [root@zlz mysql]# mysqld --user=mysql --basedir=/usr/local/mysql2. --datadir=/data3307 --initialize3. ...4. 2018-09-26T07:07:33.443378Z 1 [Note] A temporary password is generated for root@localhost: 7L?Vi!dGKmgu //root用户登录的初始化密码5. [root@zlz mysql]# mysqld --user=mysql --basedir=/usr/local/mysql6. --datadir=/data3308 --initialize7. ...8. 2018-09-26T07:08:07.770289Z 1 [Note] A temporary password is generated for root@localhost: kC)BbyUp1a-b //root用户登录的初始化密码
7）启动多实例
1. [root@zlz mysql]# mysqld_multi start 1 //1为实例编号2. [root@zlz mysql]# mysqld_multi start 2
8）查看端口
1. [root@zlz mysql]# netstat -utnlp | grep :33072. tcp6 0 0 :::3307 :::* LISTEN 21009/mysqld3. [root@zlz mysql]# netstat -utnlp | grep :33084. tcp6 0 0 :::3308 :::* LISTEN 21177/mysqld5. [root@zlz mysql]# ps -C mysqld6. PID TTY TIME CMD7. 21009 pts/1 00:00:00 mysqld8. 21177 pts/1 00:00:00 mysqld
9）访问多实例
使用初始化密码登录多实例1
1. [root@zlz mysql]# mysql -u root -p\'7L?Vi!dGKmgu\' -S /data3307/mysqld.sock2. mysql> alter user root@\"localhost\" identified by \'123456\'; //修改密码3. mysql> show databases;4. +--------------------+5. | Database |6. +--------------------+7. | information_schema |8. | mysql |9. | performance_schema |10. | sys |11. +--------------------+12. 4 rows in set (0.00 sec)
使用初始化密码登录多实例2
1. [root@zlz bin]# mysql -u root -p\'kC)BbyUp1a-b\' -S /data3307/mysqld.sock2. mysql> alter user root@\"localhost\" identified by \'123456\'; //修改密码3. mysql> show databases;4. +--------------------+5. | Database |6. +--------------------+7. | information_schema |8. | mysql |9. | performance_schema |10. | sys |11. +--------------------+12. 4 rows in set (0.00 sec)
10）创建库
1. mysql> create database db1;2. Query OK, 1 row affected (0.00 sec)3. mysql> show databases;4. +--------------------+5. | Database |6. +--------------------+7. | information_schema |8. | db1 |9. | mysql |10. | performance_schema |11. | sys |12. +--------------------+13. 5 rows in set (0.00 sec)
11）停止启动的实例服务
mysqld_multi –user=root –password=密码 stop 实例编号
1. [root@zlz mysql]# mysqld_multi --user=root --password=123456 stop 12. [root@zlz mysql]# netstat -utnlp | grep :3307 //查看没有端口3. [root@zlz mysql]# mysqld_multi --user=root --password=123456 stop 24. [root@zlz mysql]# netstat -utnlp | grep :3308 //查看没有端口5. [root@zlz mysql]# mysql -uroot -p123456 -S /data3307/mysqld.sock6. //拒绝连接7. mysql: [Warning] Using a password on the command line interface can be insecure.8. ERROR 2002 (HY000): Can\'t connect to local MySQL server through socket \'/data3307/mysqld.sock\' (2)
3 案例3：MySQL性能优化
3.1 问题
• 练习相关优化选项
• 启用慢查询日志
• 查看各种系统变量、状态变量
3.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：mysql性能优化
1）查看服务运行时的参数配置
1. mysql> show variables\\G;2. ......3. *************************** 171. row ***************************4. Variable_name: innodb_log_file_size5. Value: 503316486. *************************** 172. row ***************************7. Variable_name: innodb_log_files_in_group8. Value: 29. *************************** 173. row ***************************10. Variable_name: innodb_log_group_home_dir11. Value: ./12. *************************** 174. row ***************************13. Variable_name: innodb_log_write_ahead_size14. Value: 819215. *************************** 175. row ***************************16. Variable_name: innodb_lru_scan_depth17. Value: 102418. *************************** 176. row ***************************19. Variable_name: innodb_max_dirty_pages_pct20. Value: 75.00000021. *************************** 177. row ***************************22. Variable_name: innodb_max_dirty_pages_pct_lwm23. Value: 0.00000024. *************************** 178. row ***************************25. Variable_name: innodb_max_purge_lag26. Value: 027. ......28.29. mysql> show variables like \"%innodb%\";30. +------------------------------------------+------------------------+31. | Variable_name | Value |32. +------------------------------------------+------------------------+33. | ignore_builtin_innodb | OFF |34. | innodb_adaptive_flushing | ON |35. | innodb_adaptive_flushing_lwm | 10 |36. | innodb_adaptive_hash_index | ON |37. | innodb_adaptive_hash_index_parts | 8 |38. | innodb_adaptive_max_sleep_delay | 150000 |39. ......40. ......41. | innodb_undo_log_truncate | OFF |42. | innodb_undo_logs | 128 |43. | innodb_undo_tablespaces | 0 |44. | innodb_use_native_aio | ON |45. | innodb_version | 5.7.17 |46. | innodb_write_io_threads | 4 |47. +------------------------------------------+------------------------+48. 134 rows in set (0.01 sec)
2）并发连接数量
查看当前已经使用的连接数
1. mysql> flush status;2. Query OK, 0 rows affected (0.00 sec)3. mysql> show global status like \"Max_used_connections\";4. +----------------------+-------+5. | Variable_name | Value |6. +----------------------+-------+7. | Max_used_connections | 3 |8. +----------------------+-------+9. 1 row in set (0.00 sec)
查看默认的最大连接数
1. mysql> show variables like \"max_connections%\";2. +-----------------+-------+3. | Variable_name | Value |4. +-----------------+-------+5. | max_connections | 151 |6. +-----------------+-------+7. 1 row in set (0.00 sec)
3）连接超时时间
1. mysql> show variables like \"%timeout%\";2. +-----------------------------+----------+3. | Variable_name | Value |4. +-----------------------------+----------+5. | connect_timeout | 10 |6. | delayed_insert_timeout | 300 |7. | have_statement_timeout | YES |8. | innodb_flush_log_at_timeout | 1 |9. | innodb_lock_wait_timeout | 50 |10. | innodb_rollback_on_timeout | OFF |11. | interactive_timeout | 28800 |12. | lock_wait_timeout | 31536000 |13. | net_read_timeout | 30 |14. | net_write_timeout | 60 |15. | rpl_stop_slave_timeout | 31536000 |16. | slave_net_timeout | 60 |17. | wait_timeout | 28800 |18. +-----------------------------+----------+19. 13 rows in set (0.00 sec)
4）允许保存在缓存中被重用的线程数量
1. mysql> show variables like \"thread_cache_size\";2. +-------------------+-------+3. | Variable_name | Value |4. +-------------------+-------+5. | thread_cache_size | 9 |6. +-------------------+-------+7. 1 row in set (0.00 sec)
5）用于MyISAM引擎的关键索引缓存大小
1. mysql> show variables like \"key_buffer_size\";2. +-----------------+---------+3. | Variable_name | Value |4. +-----------------+---------+5. | key_buffer_size | 8388608 |6. +-----------------+---------+7. 1 row in set (0.00 sec)
6）为每个要排序的线程分配此大小的缓存空间
1. mysql> show variables like \"sort_buffer_size\";2. +------------------+--------+3. | Variable_name | Value |4. +------------------+--------+5. | sort_buffer_size | 262144 |6. +------------------+--------+7. 1 row in set (0.00 sec)
7）为顺序读取表记录保留的缓存大小
1. mysql> show variables like \"read_buffer_size\";2. +------------------+--------+3. | Variable_name | Value |4. +------------------+--------+5. | read_buffer_size | 131072 |6. +------------------+--------+7. 1 row in set (0.01 sec)
8）为所有线程缓存的打开的表的数量
1. mysql> show variables like \"table_open_cache\";2. +------------------+-------+3. | Variable_name | Value |4. +------------------+-------+5. | table_open_cache | 2000 |6. +------------------+-------+7. 1 row in set (0.00 sec)
步骤二：SQL查询优化
1）常用日志种类及选项，如图-1所示：
图-1
记录慢查询，图-2所示：
启用慢查询日志
1. [root@zlz ~]# vim /etc/my.cnf2. ...3. slow_query_log=14. slow_query_log_file=mysql-slow.log5. long_query_time=56. log_queries_not_using_indexes=17. ...8. [root@zlz ~]# systemctl restart mysqld
2）查看慢查询日志
1. [root@zlz ~]# mysqldumpslow /var/lib/mysql/mysql-slow.log2.3. Reading mysql slow query log from /var/lib/mysql/mysql-slow.log4. Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
查看缓存的大小
1. mysql> show variables like \"query_cache%\";2. +------------------------------+---------+3. | Variable_name | Value |4. +------------------------------+---------+5. | query_cache_limit | 1048576 |6. | query_cache_min_res_unit | 4096 |7. | query_cache_size | 1048576 |8. | query_cache_type | OFF |9. | query_cache_wlock_invalidate | OFF |10. +------------------------------+---------+11. 5 rows in set (0.00 sec)
3）查看当前的查询缓存统计
1. mysql> show global status like \"qcache%\";2. +-------------------------+---------+3. | Variable_name | Value |4. +-------------------------+---------+5. | Qcache_free_blocks | 1 |6. | Qcache_free_memory | 1031832 |7. | Qcache_hits | 0 |8. | Qcache_inserts | 0 |9. | Qcache_lowmem_prunes | 0 |10. | Qcache_not_cached | 40 |11. | Qcache_queries_in_cache | 0 |12. | Qcache_total_blocks | 1 |13. +-------------------------+---------+14. 8 rows in set (0.00 sec)
