Mysql由浅入深
2. Mysql的多实例
1. Mysql多实例共用一套Mysql安装程序，使用不同的my.cnf配置文件，启动程序，数据文件。
多实例逻辑上是独立的，但是实际上使用的是同一台服务器资源。
nginx,apache,haproxy,redis,memcache，都可以多实例。
2. Mysql多实例的作用与问题
1. 有效利用服务器资源
2. 节约服务器资源
3. 问题：当某个实例并发很高或者有慢查询的时候，整个实例会消耗服务器的CPU，内存，磁盘IO资源。
3. Mysql多实例的应用场景
1. 资金紧张型公司
2. 并发访问不是特别大的业务
3. 百度搜索引擎的数据库就是多实例，一般是从库。
4. Mysql多实例的多种配置方案
1. 多个配置文件多个启动程序多个数据文件
pkill mysqld 杀死原有的mysqld
mkdir -p /data/{3306,3307}/data 创建多个实例的数据文件
2. 单一配置文件
缺点：耦合性太高
5. 配置过程
1. 编译安装完成mysql-5.6.40以后，开始创建配置文件和数据文件和启动文件。
mkdir -p /data/{3306,3307}/data
2. 在/data/3306和/data/3307目录下创建多实例的配置文件内容
[mysqld] basedir=/application/mysql datadir=/data/3306/data server-id=3306 port=3306 log-bin=/data/3306/mysql-bin socket=/data/3306/mysql.sock max_allowed_packet=8M sort_buffer_size=1M join_buffer_size=1M max_connections=800 max_connect_errors=3000 #table_cache=614 external-locking=FALSE thread_cache_size=100 thread_concurrency=2 query_cache_size=2M query_cache_limit=1M query_cache_min_res_unit=2k ft_min_word_len = 4 default-storage-engine = InnoDB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M binlog_format=mixed slow_query_log long_query_time = 2 key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:10M:autoextend innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqld_safe] log-error=/data/3306/mysql_oldboy3306.err pid-file=/data/3306/mysqld.pid
[mysqld] basedir=/application/mysql datadir=/data/3307/data server-id=3307 port=3307 log-bin=/data/3307/mysql-bin socket=/data/3307/mysql.sock max_allowed_packet=8M sort_buffer_size=1M join_buffer_size=1M max_connections=800 max_connect_errors=3000 #table_cache=614 external-locking=FALSE thread_cache_size=100 thread_concurrency=2 query_cache_size=2M query_cache_limit=1M query_cache_min_res_unit=2k ft_min_word_len = 4 default-storage-engine = InnoDB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M binlog_format=mixed slow_query_log long_query_time = 2 key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:10M:autoextend innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqld_safe] log-error=/data/3307/mysql_oldboy3307.err pid-file=/data/3307/mysqld.pid
3. 启动文件内容/data/3306/mysql和/data/3307/mysql
[root@nfs-server 3306]# cat mysql
#!/bin/bash
###################################################
#yangjianbo created
port=3306
mysql_user="root"
mysql_pwd="123"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#start function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting Mysql...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
else
printf "Mysql is running...\n"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Mysql is stopped...\n"
exit
else
printf "Stopping Mysql...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
printf "Restarting Mysql...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
[root@nfs-server 3307]# cat mysql
#!/bin/bash
###################################################
#yangjianbo created
port=3307
mysql_user="root"
mysql_pwd="123"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#start function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting Mysql...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
else
printf "Mysql is running...\n"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Mysql is stopped...\n"
exit
else
printf "Stopping Mysql...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
printf "Restarting Mysql...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
4. 初始化多实例的数据库文件
cd /application/mysql/scripts
./mysql_install_db --defaults-file=/data/3306/my.cnf --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
./mysql_install_db --defaults-file=/data/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
出现下面的结果：
出现两个OK，没有error级别的错误即可。
初始化的目的：创建基础的数据库系统的库文件。创建完成后，会在/data/3306/data和/data/3307/data目录下创建很多文件。
5. 启动多实例
/data/3306/mysql start
/data/3307/mysql start
启动的时候报错：
Starting MySQL.170814 20:02:33 mysqld_safe error: log-error set to '/data/3306/mysql_oldboy3306.err', however file don't exists. Create writable for user 'mysql'.
解决方法：
手工创建该文件 touch /data/3306/mysql_oldboy3306.err
授权 chown mysql.mysql /data/3306/mysql_oldboy3306.err
6. 检查是否启动成功
lsof -i:3306
lsof -i:3307
netstat -lntup | grep 330
7. 多实例排错
查看log-error日志
/data/3306/mysql_oldboy3306.err
8. 设置root密码
第一次登录，是没有密码的。5.7以后需要密码。
mysqladmin -u root -S /data/3306/mysql.sock password '123'
mysqladmin -u root -S /data/3306/mysql.sock password '123'
9. 登录多实例
本地登录
mysql -uroot -p -S /data/3306/mysql.sock
远程登录
mysql -uroot -p -h 数据库IP -P 3307
10. 设置开机启动
centos6
添加到/etc/rc.local文件中
/data/3306/mysql start
/data/3307/mysql start
centos7
添加到systemd中
11. 如何再增加一个mysql实例
创建对应的目录
复制配置文件和启动文件，并修改内容
初始化数据库
授权
启动mysql
设置root密码
3. Mysql的帮助命令help
1. 与linux的man命令一样
进入mysql实例中，然后执行help show
12. 生产环境快速配置mysql主从复制
1. 配置好从库，设置好log-bin和server-id参数
2. 无需配置主库，因为主库已经上线，log-bin和server-id已经配置好了。
3. 在主库创建帐号，用来主从复制。
4. 使用mysqldump -uroot -p'123456' -S /data/3306/mysql.sock -A --events -B -x --master-data=1 | gzip >/opt/$(date +%F).sql.gz
5. 只需要在从库上导入全备，change master的时候不需要指定binlog文件名和位置。
13. 一键批量创建从库
1. 还原备份到从库上
2. 写脚本执行change master
3. 写脚本执行start slave
4. 检查主从状态
14. mysql主从复制三个线程状态
1. 主库：
show processlist \G;
*************************** 2. row ***************************
Id: 4
User: rep
Host: nfs-server:35652
db: NULL
Command: Binlog Dump
Time: 2246
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
2. 从库
show processlist \G;
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 2277
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 2285
State: Waiting for master to send event
Info: NULL
show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.200
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes 从库的IO线程
Slave_SQL_Running: Yes 从库的SQL线程
Replicate_Do_DB:
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: 620
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: 0 落后主库的秒数
Master_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: 3306
Master_UUID: a027a03c-7df9-11e8-92fc-000c296cbf3b
Master_Info_File: /data/3307/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
15. mysql错误代码
MySQL常见错误代码及代码说明 1005：创建表失败 1006：创建数据库失败 1007：数据库已存在，创建数据库失败<=================可以忽略 1008：数据库不存在，删除数据库失败<=================可以忽略 1009：不能删除数据库文件导致删除数据库失败 1010：不能删除数据目录导致删除数据库失败 1011：删除数据库文件失败 1012：不能读取系统表中的记录 1020：记录已被其他用户修改 1021：硬盘剩余空间不足，请加大硬盘可用空间 1022：关键字重复，更改记录失败 1023：关闭时发生错误 1024：读文件错误 1025：更改名字时发生错误 1026：写文件错误 1032：记录不存在<=============================可以忽略 1036：数据表是只读的，不能对它进行修改 1037：系统内存不足，请重启数据库或重启服务器 1038：用于排序的内存不足，请增大排序缓冲区 1040：已到达数据库的最大连接数，请加大数据库可用连接数 1041：系统内存不足 1042：无效的主机名 1043：无效连接 1044：当前用户没有访问数据库的权限 1045：不能连接数据库，用户名或密码错误 1048：字段不能为空 1049：数据库不存在 <=============================可以忽略 1050：数据表已存在 1051：数据表不存在 1054：字段不存在 1062：字段值重复，入库失败<==========================可以忽略 1065：无效的SQL语句，SQL语句为空 1081：不能建立Socket连接 1114：数据表已满，不能容纳任何记录 1116：打开的数据表太多 1129：数据库出现异常，请重启数据库 1130：连接数据库失败，没有连接数据库的权限 1133：数据库用户不存在 1141：当前用户无权访问数据库 1142：当前用户无权访问数据表 1143：当前用户无权访问数据表中的字段 1146：数据表不存在 <=============================可以忽略 1147：未定义用户对数据表的访问权限 1149：SQL语句语法错误 1158：网络错误，出现读错误，请检查网络连接状况 1159：网络错误，读超时，请检查网络连接状况 1160：网络错误，出现写错误，请检查网络连接状况 1161：网络错误，写超时，请检查网络连接状况 1169：字段值重复，更新记录失败 1177：打开数据表失败 1180：提交事务失败 1181：回滚事务失败 1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库 1205：加锁超时 1211：当前用户没有创建用户的权限 1216：外键约束检查失败，更新子表记录失败 1217：外键约束检查失败，删除或修改主表记录失败 1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器 1227：权限不足，您无权进行此操作 1235：MySQL版本过低，不具有本功能
16. mysql主从复制读写分离授权方案
1. 生产授权方案1
主：web oldboy123 10.0.0.1 3306 (select,insert,delete,update)
从：主库的web用户同步到从库，然后回收insert,delete,update权限。
不收回从库权限，设置read-only参数确保从库只读。
2. 生产授权方案2
主：web_w oldboy123 10.0.0.1 3306 (select,insert,delete,update)
从：web_r oldboy123 10.0.0.2 3306 (select)
风险：web_w连接从库，设置read-only参数确保从库只读
3. 生产授权方案3
不同步授权库mysql
17. 忽略授权表
1. 在/data/3306/my.cnf配置文件的mysqld模块中，添加如下信息：
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
注意：是在主库上添加的参数。这样就不会复制到从库，减少I/O。
18. mysql主从复制指定库的复制
1. 在配置文件中，添加binlog-do-db = yangjianbo,test
19. mysql主从复制只读
1. 在配置文件的mysqld中，添加：
read-only
重启mysql服务。
2. 注意：具有SUPER权限的用户可以更新，不受read-only参数影响
来自从服务器线程可以更新，例如创建的rep账户。
20. mysql主从复制故障
1. 从库已经存在的数据库，主库无法同步同名的数据库。
查看show slave status\G;
Last_SQL_Error: Error 'Can't create database 'wangyanhe'; database exists' on query. Default database: 'wangyanhe'. Query: 'create database wangyanhe'
解决方法一：
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
解决方法二：
提前把错误号写入到my.cnf配置文件中。
slave-skip-errors = 1032,1062,1007
2. mysql连接慢
skip-name-resolve
21. mysql从库记录binlog日志的方法
1. 级联同步
2. 从库作为备份服务器
3. 启动log-bin = /data/3307/mysql-bin
log-slave-updates
expire_logs_days = 7
23. 从库宕机
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.0.200',
start slave;
24. 双主及多主
1. 配置my.cnf的自增长
2. 主库与从库做个反向主从复制
16. mysql参数-e
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;
17. show processlist;
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show full processlist;"
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show processlist;"
18. show variables; 查看数据库的参数信息
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables;" | grep log_bin
19. show global status; 查看整个数据库运行状态信息，很重要
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show global status;" | grep insert
20. show status; 查看当前会话状态信息
21. show full processlist; 查看正在运行的完整sql语句。重要
27. --master-data作用
1. --master-data=2 带注释change master to
2. --master-data=1 用于主从复制
4. relay log 中继日志
在接收端的日志
测试benchmark(court,expr) 还需要研究
select benchmark(50000000,2*3);
38. mysql语句优化分析
1. sql性能下降（执行时间长，等待时间长）
1. 查询语句写的不好,没有索引
2. 索引失效，索引已创建，但是没用上。
3. 关联查询join过多（设计缺陷及不得已的需求）
4. 服务器调优及各个参数设置
2. sql执行顺序
手写
select
distinct <type_list>
from <left_table>
join <right_table> on <join_condition>
where
<where_condition>
group by
<group_by_list>
Having
<having_condition>
order by
<order_by_condition>
limit
<limit_number>
机读
from <left_table>
on <join_condition>
join <right_table>
where
<where_condition>
group by
<group_by_list>
Having
<having_condition>
select
distinct <type_list>
order by
<order_by_condition>
limit
<limit_number>
总结
3. join使用
1. 七种join方式
39. mysql视图
40. mysql触发器
41. mysql存储过程
42. mysql集群技术
43. mysql字符集
1. 查看字符集
show variables like 'char%';
show global variables like 'char%';
2. 修改字符集
临时生效 mysql重启后，失效。
alter database mydb character set utf-8;
永久生效
修改配置文件/etc/my.cnf
[mysqld] character_set_server = utf8 character_set_client = utf8 [client] default-character-set=utf8
重启mysql服务
45. mysql监控
1. 对数据库服务可用性监控
1. mysqladmin -umonitor_user -p -h ping
2. telnet ip db_port
3. 使用程序通过网络建立数据库连接
4. 检查数据库的read_only参数是否为off
5. 建立监控表并对表中数据进行更改
6. 执行简单的查询select @@version
7. 监控数据库的连接数
show variables like 'max_connections'; 查看数据库支持的最大连接数
show global status like 'Threads_connected'; 查看当前连接数
Threads_connected / max_connections > 0.8
2. 对数据库性能监控
1. 如何计算QPS和TPS
QPS=()
TPS=()
2. 如何监控数据库的并发请求数量
show global status like 'Threads_running'
并发处理的数量通常会远小于同一时间连接到数据库的线程的数量
3. 如何监控Innodb的阻塞
3. 对主从复制进行监控
4. 对服务器资源的监控
47. 如何查看阻塞的进程
1. 模拟环境
第一个会话
begin;
update test_lock_innodb set b='houzi' where a=3;
第二个会话修改同样一条数据
update test_lock_innodb set b='lexiang' where a=3; 结果被阻塞
2. 查看被阻塞的语句
SELECT b.trx_mysql_thread_id AS 'blocked_thread_id'
,b.trx_query AS 'blocked_sql_text'
,c.trx_mysql_thread_id AS 'blocker_thread_id'
,c.trx_query AS 'blocker_sql_text'
,( Unix_timestamp() - Unix_timestamp(c.trx_started) )
AS 'blocked_time'
FROM information_schema.innodb_lock_waits a
INNER JOIN information_schema.innodb_trx b
ON a.requesting_trx_id = b.trx_id
INNER JOIN information_schema.innodb_trx c
ON a.blocking_trx_id = c.trx_id
WHERE ( Unix_timestamp() - Unix_timestamp(c.trx_started) ) > 4;
+-------------------+-------------------------------------------------+-------------------+------------------+--------------+ | blocked_thread_id | blocked_sql_text | blocker_thread_id | blocker_sql_text | blocked_time | +-------------------+-------------------------------------------------+-------------------+------------------+--------------+ | 41587976 | update test_lock_innodb set b='houzi' where a=3 | 41596425 | NULL | 24 | +-------------------+-------------------------------------------------+-------------------+------------------+--------------+
3. 查看阻塞源是谁
SELECT a.sql_text,
c.id,
d.trx_started
FROM performance_schema.events_statements_current a
join performance_schema.threads b
ON a.thread_id = b.thread_id
join information_schema.processlist c
ON b.processlist_id = c.id
join information_schema.innodb_trx d
ON c.id = d.trx_mysql_thread_id
where c.id=17
ORDER BY d.trx_started;
c.id修改为被阻塞的语句的线程ID
浙公网安备 33010602011771号