mysql基础知识学习（mysql使用工作笔记非常详细）

purge binary logs to 'mysql-bin.000354'; #清理mysql binlog日志SET GLOBAL expire_logs_days = 3; #设置binlog保留3天FLUSH LOGS;show variables like 'expire_logs_days';

mysql -u root -p> purge master logs to 'mysql-bin.010’; //清除mysql-bin.010日志> purge master logs before '2020-02-28 13:00:00'; //清除2020-02-28 13:00:00前的日志> purge master logs before date_sub(now(), interval 90 day); //清除90天前的bin日志

数据库备份

备份所有数据库 mysqldump -uroot -p --all-databases > all.sql

备份数据库test mysqldump -uroot -p test > test.sql

备份数据库test下的表emp mysqldump -uroot -p test emp > emp.sql

备份数据库test下的表emp和dept mysqldump -uroot -p test emp dept > emp_dept.sql

备份数据库test下的所有表为逗号分割的文本，备份到/tmp: mysqlddump -uroot -p -T /tmp test emp --fields-terminated-by ','shell> more emp.txt 1,z12,z23,z34,z4

# 备份全部数据库（包含存储过程、自定义函数及事件）mysqldump -uroot -p123456 --single-transaction -R -E --all-databases > /tmp/all_database.sql

# 要求记录 binlog 位点信息 可用于搭建从库mysqldump -uroot -p123456 --single-transaction -R -E --all-databases --master-data=2 > /tmp/all_database.sql

# 备份指定数据库mysqldump -uroot -p123456 --single-transaction -R -E --databases db1 > /tmp/db1.sqlmysqldump -uroot -p123456 --single-transaction -R -E --databases db1 db2 > /tmp/db1_db2.sql

# 备份部分表mysqldump -uroot -p123456 --single-transaction db1 tb1 > /tmp/tb1.sqlmysqldump -uroot -p123456 --single-transaction db1 tb1 tb2 tb3 > /tmp/tb.sql

# 导出某个表，数据以单行insert显示mysqldump -uroot -p123456 --single-transaction --skip-extended-insert db1 tb1 > /tmp/tb1.sql

# 导出单表的部分数据mysqldump -uroot -p123456 --single-transaction db1 tb1 --where=" create_time >= '2021-06-01 00:00:00' " > /tmp/tb1.sqlmysqldump -uroot -p123456 --single-transaction db1 tb1 --where='id < 10' > /tmp/tb1.sql

# 排除某些表导出mysqldump -uroot -p123456 --single-transaction --databases db1 --ignore-table=db1.tb1 --ignore-table=db1.tb2 > /tmp/db1.sql

# 只导出结构或只导出数据mysqldump -uroot -p123456 db1 --no-data > /tmp/db1_jiegou.sqlmysqldump -uroot -p123456 db1 --no-create-info > /tmp/db1_data.sql

# 只导出某个库的存储过程及自定义函数mysqldump -uroot -p123456 -d -t -R db1 > /tmp/db1_routine.sql

# 远程导出 即MySQL服务端不在本地mysqldump -uroot -p123456 -h192.168.1.10 -P3306 --single-transaction --databases db1 > /tmp/db1.sql

#只导出除了系统库外的所有库数据 mysql -uroot -p'123456' -e "show databases"|grep -Ev "Database|mysql|information_schema|performance_schema|sys"|xargs mysqldump -uroot -p --routines --single_transaction --master-data=2 --databases > mysql_dump.sql

#导出所有数据库 mysqldump -uroot -p'123456' --routines --single_transaction --master-data=2 --all-databases > mysql_dump.sql

mysql分区表过大，配置自动回收innodb_max_undo_log_size = 4Ginnodb_undo_log_truncate = ONinnodb_undo_tablespaces = 3

mysql> show global variables like 'table_open_cache';mysql> show global status like 'Open%tables';mysql> set global table_open_cache=6144;清空表缓存mysql>flush tables; 可以将open_tables 清零systemctl restart mysqld 可以讲opened_tables 清零

数据库恢复

mysql -uroot -p db_name < backfile

使用 mysqlbinlog 恢复自 mysqldump 备份以来的 binglog ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000002 | ./mysql t2

基于时间点恢复 上午 10 点发生了误操作 mysqlbinlog --stop-date="2017-09-30 9:59:59" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword

mysqlbinlog --start-date="2017-09-30 10:01:00" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword

基于位置恢复

mysqlbinlog --start-date="2019-09-30 9:59:59" --stop-date="2019-09-30 10:01:00"/data/mysql/mysql-bin.123456 > /tmp/mysql_restore.sql

mysqlbinlog --stop-position="368312" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword

mysqlbinlog --start-position="368315" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword

表的导出 select * from test into outfile '/data/mysql/outfile.txt' fields terminated by "," enclosed by '"';