目录
4.1、1040，Can not connect to MySQL server. Too many connections
1、事务锁表
1、查询是否锁表
show OPEN TABLES where In_use > 0;2、查看当前的事务
SELECT * FROM information_schema.INNODB_TRX;3、查看锁
select * from information_schema.INNODB_LOCKS;4、查看锁等待
select * from information_schema.INNODB_LOCK_WAITS;5、查询进程 ##查询到相对应的进程===然后 kill id
show processlist;
show status like 'innodb_row_lock_%';
2、表大小、数量
select table_name as '表名',concat(round(sum(data_length/1024/1024),2),'MB') as '占用大小(MB)',SUM(table_rows) as '数据量' from information_schema.`TABLES` where table_schema="teahouse" GROUP BY table_name ORDER BY sum(data_length) desc;
3、数据恢复
mysqlbinlog语法：
mysqlbinlog [options] log-files
options：可选参数
log-files：文件名称
[options]常用值：
-d: 根据数据库的名称筛选日志
-o：跳过前N行日志
-r, --result-fil: 把日志输出到指定文件
–start-datetime: 读取指定时间之后的日志，时间格式：yyyy-MM-dd HH:mm:ss
–stop-datetime: 读取指定时间之前的日志，时间格式：yyyy-MM-dd HH:mm:ss
–start-position: 从指定位置开始读取日志
–stop-position: 读取到指定位置停止
–base64-output：在row格式下，显示伪sql语句
-v, --verbose：显示伪sql语句-vv可以为sql语句添加备注
–set-charset：设置输出文件编码
3.1、mysqlbinlog日志根据时间提取sql（不可读）加（ --base64-output=decode-rows -v 部分可读
mysqlbinlog.exe --start-datetime="2021-03-01 00:00:00" --stop-datetime="2021-03-01 23:59:59" --database=表名 ZBLTP0VIP0075-bin.000008 > 1.sql
3.2、mysqlbinlog日志根据位置提取sql（不可读）加（ --base64-output=decode-rows -v 部分可读）
mysqlbinlog.exe --start-position 750 --stop-position 1037 -vv --database=table_name ZBLTP0VIP0075-bin.000006 > 1.sql
实际操作：
1、查看binlog日志是否开启和存放目录2、查看mysqlbinlog日志列表##########################还原是否锁表，看个人意见################################## #全局锁表(只读) FLUSH TABLES WITH READ LOCK; #单个表加锁(只读) flush tables table_name with read lock ; #释放锁 UNLOCK TABLE; ######################################################### 1、查看binlog日志目录 mysql> SHOW VARIABLES LIKE '%log_bin%'; +---------------------------------+---------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+---------------------------------------+3、刷新mysqlbinlog日志（首先为了防止干扰，执行 flush logs ，产生一个新binlog 文件。）mysql> SHOW MASTER logs; +------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | mysql-bin.000001 | 180 | No | | mysql-bin.000002 | 180 | No | | mysql-bin.000003 | 180 | No | | mysql-bin.000004 | 7971 | No | | mysql-bin.000005 | 792 | No | +------------------+-----------+-----------+ 5 rows in set (0.00 sec)
4、进入目录#生成新的binlog日志文件 mysql> FLUSH logs; Query OK, 0 rows affected (0.01 sec) #再次查看发现多一个binlog日志文件 mysql> SHOW MASTER logs; +------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | mysql-bin.000001 | 180 | No | | mysql-bin.000002 | 180 | No | | mysql-bin.000003 | 180 | No | | mysql-bin.000004 | 7971 | No | | mysql-bin.000005 | 792 | No | | mysql-bin.000006 | 157 | No | +------------------+-----------+-----------+ 6 rows in set (0.00 sec) #查看mysqlbinlog日志 mysql> SHOW BINLOG EVENTS IN "mysql-bin.000005"; +------------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+--------------------------------------+ | mysql-bin.000005 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.31, Binlog ver: 4 | | mysql-bin.000005 | 126 | Previous_gtids | 1 | 157 | | | mysql-bin.000005 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000005 | 236 | Query | 1 | 311 | BEGIN | | mysql-bin.000005 | 311 | Table_map | 1 | 370 | table_id: 114 (test.demo) | | mysql-bin.000005 | 370 | Delete_rows | 1 | 422 | table_id: 114 flags: STMT_END_F | | mysql-bin.000005 | 422 | Xid | 1 | 453 | COMMIT /* xid=203 */ | | mysql-bin.000005 | 453 | Anonymous_Gtid | 1 | 532 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000005 | 532 | Query | 1 | 607 | BEGIN | | mysql-bin.000005 | 607 | Table_map | 1 | 666 | table_id: 114 (test.demo) | | mysql-bin.000005 | 666 | Delete_rows | 1 | 714 | table_id: 114 flags: STMT_END_F | | mysql-bin.000005 | 714 | Xid | 1 | 745 | COMMIT /* xid=204 */ | | mysql-bin.000005 | 745 | Rotate | 1 | 792 | mysql-bin.000006;pos=4 | +------------------+-----+----------------+-----------+-------------+--------------------------------------+ 13 rows in set (0.00 sec)5、操作binlog#进入binlog日志存放的文件夹 [root@node1 /]# cd /usr/local/mysql/data [root@node1 data]# ll 总用量 90580 -rw-r----- 1 mysql mysql 180 2月 28 22:38 mysql-bin.000001 -rw-r----- 1 mysql mysql 180 2月 28 22:45 mysql-bin.000002 -rw-r----- 1 mysql mysql 180 2月 28 23:11 mysql-bin.000003 -rw-r----- 1 mysql mysql 7924 2月 28 23:44 mysql-bin.000004 -rw-r----- 1 mysql mysql 792 2月 28 23:44 mysql-bin.000005 -rw-r----- 1 mysql mysql 76 2月 28 23:11 mysql-bin.index
5.1、恢复误删（数据未更改，且可以找到创建的sql）找到要恢复的数据偏移量（534——739）[root@node1 bin]# pwd /usr/local/mysql/bin 1、根据时间模糊截取（如果指定误删时间开始和结束时间，减少排查时间） [root@node1 bin]# ./mysqlbinlog -vv --base64-output=decode-rows --start-datetime="2023-03-01 18:00:00" --stop-datetime="2023-03-01 18:30:59" /usr/local/mysql/data/mysql-bin.000018 > /home/1.sql
2、根据上图偏移量精确截取成功文件，注意要还原的文件不要加（-vv --base64-output=decode-rows）否则无法还原 [root@node1 bin]# ./mysqlbinlog --start-position=534 --stop-position=739 /usr/local/mysql/data/mysql-bin.000018 > /home/1.sql 3、导入mysql mysql> source /home/1.sql;
5.2、恢复变更过的数据(历史数据：误删，字段更新错误....)#导出指定时间内的binlog日志信息 [root@node1 bin]# ./mysqlbinlog -vv --base64-output=decode-rows --start-datetime="2023-03-01 00:00:00" --stop-datetime="2023-03-01 18:30:59" /usr/local/mysql/data/mysql-bin.000019 > /home/1.sql #查看导出binlog日志信息 [root@node1 home]# cat 1.sql # The proper term is pseudo_replica_mode, but we use this compatibility alias ...... #通过sql命令查看日志信息 mysql> SHOW BINLOG EVENTS IN "mysql-bin.000019"; +------------------+------+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+--------------------------------------+ | mysql-bin.000019 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.31, Binlog ver: 4 | | mysql-bin.000019 | 126 | Previous_gtids | 1 | 157 | | ...... +------------------+------+----------------+-----------+-------------+--------------------------------------+ 53 rows in set (0.00 sec)
通过上面信息可知更改或删除前的数据（然后可以采用以下两种方式还原）
5.2.1、可以手动拼接还原sql（数据量少）
5.2.2、也可以借助工具拼接sql（如 my2sql）#正向sql ./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -databases test -tables demo -mode repl -work-type 2sql -start-file /mysql-bin.000017 -start-datetime "2023-01-02 15:02:00" -stop-datetime "2023-03-02 15:05:00" -output-dir /home/sql/ 逆向sql ./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -databases test -tables demo -mode repl -work-type rollback -start-file /mysql-bin.000017 -start-datetime "2023-01-02 15:02:00" -stop-datetime "2023-03-02 15:05:00" -output-dir /home/sql
4、异常
4.1、1040，Can not connect to MySQL server. Too many connections
MySQL连接数默认是151，在开发过程中出现过多的连接处于sleep状态未被释放，超过了默认的最大连接数，然后报错连接数过多。如果完全连不上，那就停掉占用连接的应用，停了还是连不上就重启MySQL。如果连的上那就可以使用下面的SQL来操作。下面这些手段都是在数据库本身做限制，真正要解决这个问题还是要排查出产生这么多sleep连接的原因，可能是数据库连接池参数设置的不合理、跑的应用程序过多负载太大等等原因。
mysql> show processlist; # 查看线程情况 +-----+-----------------+-----------------+-----------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-----------------+-----------+---------+------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 1757 | Waiting on empty queue | NULL | | 12 | root | localhost:43514 | infosecqa | Sleep | 538 | | NULL | | 49 | root | localhost:43540 | infosecqa | Sleep | 480 | | NULL | | 51 | root | localhost:43544 | infosecqa | Sleep | 479 |mysql> show variables like "%max_connection%"; # 查看最大连接数 +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_connections | 151 | # mysql最大允许连接数 | mysqlx_max_connections | 100 | +------------------------+-------+ 2 rows in set (0.00 sec)mysql> show status like "Thread%"; # 查看连接情况 +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 4 | | Threads_connected | 143 | # 已建立的连接数 | Threads_created | 148 | # 打开的连接数 | Threads_running | 2 | # 正在运行的连接数 +-------------------+-------+ 4 rows in set (0.00 sec)mysql> show variables like '%timeout%'; #查看相关参数 +-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | connect_timeout | 10 | #等待握手的超时时间 | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 |#事务遇到锁等待时的Query超时时间。跟死锁不一样，InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务，锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源，被回滚的肯定是请求锁的那个Query。 | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 |#服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值：28800秒（8小时） | lock_wait_timeout | 31536000 | | mysqlx_connect_timeout | 30 | | mysqlx_idle_worker_thread_timeout | 60 | | mysqlx_interactive_timeout | 28800 | | mysqlx_port_open_timeout | 0 | | mysqlx_read_timeout | 30 | | mysqlx_wait_timeout | 28800 | | mysqlx_write_timeout | 60 | | net_read_timeout | 30 |#这个参数只对TCP/IP链接有效，分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间，这是在Activity状态下的线程才有效的参数 | net_write_timeout | 60 |#这个参数只对TCP/IP链接有效，分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间，这是在Activity状态下的线程才有效的参数 | replica_net_timeout | 60 | | rpl_stop_replica_timeout | 31536000 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 |#这是Slave判断主机是否挂掉的超时设置，在设定时间内依然没有获取到Master的回应就人为Master挂掉了 | ssl_session_cache_timeout | 300 | | wait_timeout | 28800 |#服务器关闭非交互连接之前等待活动的秒数。默认值：28800秒（8小时） +-----------------------------------+----------+ 23 rows in set (0.00 sec)设置wait_timeout和interactive_timeout 如果在生产环境数据库无法重启可以暂时使用root账户来设置这两个参数，但是数据库重启就会恢复默认值，要永久生效还是要在MySQL的配置文件中修改 #不重启修改wait_timeout和interactive_timeout，时间单位是秒，设置多少时间可以根据自己的需求来 set global wait_timeout=100; set global interactive_timeout=100;#获取当前连接线程状态 mysql> select * from information_schema.processlist where Command = 'Sleep' order by DB asc; +----+------+---------------------+------+---------+------+-------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+------+---------------------+------+---------+------+-------+------+ | 14 | root | 192.168.168.1:64677 | test | Sleep | 1351 | | NULL | | 15 | root | 192.168.168.1:64704 | test | Sleep | 1376 | | NULL | +----+------+---------------------+------+---------+------+-------+------+ 2 rows in set (0.00 sec) COMMAND=状态 TIME=最后跟新时间
41万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
