mysql管理维护常用操作
mysql管理维护常用的操作流程
目录
设置记录通用日志
设置记录二进制日志
查看二进制日志
备份数据库
数据备份恢复
数据库迁移
导入导出
使用sql文件创建表
批量删除表
设置记录通用日志
修改conf文件：
general_log = 1 开启通用日志
log_output=FILE,TABLE 设置保存类型
general_log_file=/usr/local**/a.log
#tabel的位置在myslq/mysql下(慢查询table也在这)
重启
设置记录二进制日志
修改conf文件：
log_bin=1 开启
log_bin_basename=/usr/local/mysql/data/binlog
log_bin_index=/usr/local/mysql/data/binlog.index
log_bin_trust_function_creators=0
log_bin_use_v1_row_events=0
sql_log_bin=1
max_binlog_cache_size
max_binlog_size
max_binlog_stmt_cache_size
重启
查看二进制日志
另存为txt方便本地查看
./mysqlbinlog /usr/local/mysql/data/binlog.000020 >a.txt
备份数据库-mysql
mysql -h 192.168.0.201 -P 20006 -uroot -pxx power> test.sql
数据备份-mysqldump
mysqldump -u root -h host -p dbname >filename
mysqldump -h 192.168.0.201 -P 20006 -uroot -pxxx --no-tablespaces --column-statistics=0 t1 > test5.sql
mysqlpump -h 192.168.0.201 -P 20006 -uroot -pxxx --default-parallelism=6 -B t1 > test55.sql
备份整个库（test01库名）
mysqldump -u root -p tets01 >test01bak.sql
备份多个库
mysqldump -u root -p --databases db1 db2 db3 >test01bak.sql
备份所有库
mysqldump -u root -p --all-databases >allbak.sql
使用mysqlhotcopy备份(只能备份MyISAM类型的表)
mysqlhotcopy db1 db2 db3 /…/文件目录
mysqldump -h 192.168.0.200 -P 20007 -uu1 -pxxx --single-transaction --no-tablespaces test >t6
高版本导出低版本
mysqldump -h cdb-9y046nah.gz.tencentcdb.com -P 10115 -uroot -pxxx --no-tablespaces --column-statistics=0 log >tlog
mysqldump -h cdb-9y046nah.gz.tencentcdb.com -P 10115 -uroot -pxxx --no-tablespaces --column-statistics=0 power_data 8A101 >ta8
数据备份-表
注意导出指定表只能针对一个数据库进行导出，且导出的内容中和导出数据库也不一样，
导出指定表的导出文本中没有创建数据库的判断语句，只有删除表-创建表-导入数据
备份某个表
mysqldump -uroot -p --databases db1 --tables a1 a2 >/tmp/db1.sql
数据备份-条件
导出db1表的a1中id=1的数据
如果多个表的条件相同可以一次性导出多个表
字段是整形
mysqldump -uroot -p --databases db1 --tables a1 --where='id=1' >/tmp/a1.sql
字段是字符串,并且导出的sql中不包含drop table,create table
mysqldump -uroot -p --no-create-info --databases db1 --tables a1 --where="id='a'" >/tmp/a1.sql
生成新的binlog文件,-F
mysqldump -uroot -p --databases db1 -F >/tmp/db1.sql
只导出表结构不导出数据，--no-data
mysqldump -uroot -p --no-data --databases db1 >/tmp/db1.sql
跨服务器导出导入数据
mysqldump --host=h1 -uroot -p --databases db1 |mysql --host=h2 -uroot -p db2
将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错
mysqldump --host=192.168.80.137 -uroot -p -C --databases test |mysql --host=192.168.80.133 -uroot -p test
加上-C参数可以启用压缩传递。
数据恢复mysqlbinlog
mysqlbinlog binlog.[0-9]* | mysql -u root -p
根据截止时间恢复（stop-datetime恢复的截止时间）
./mysqlbinlog --stop-datetime='2019-12-01 12:10:40' /.../binlog.000020 |mysql -u root -p
根据时间段恢复
./mysqlbinlog --start-datetime='...'--stop-datetime='2019-12-01 12:10:40' /.../binlog.000020 |mysql -u root -p
根据位置恢复（找到drop命令位置，跳过该命令）
./mysqlbinlog /usr/local/mysql/data/binlog.000020 --stop-position=1835 |mysql -u root -p
数据备份恢复
恢复方式
1使用mysql恢复 (如果a.sql包含库创建命令，不用指定dbname)
mysql -u root -p dbname < a.sql (‘root’：a.sql的用户)
mysql -h 192.168.0.201 -P 20006 -uroot -pxx power< test.sql
2登录mysql使用source命令恢复
>use t1;
>source /.../a.sql;
3直接恢复到数据库目录 （仅支持MyISAM类型）
4mysqlhotcopy快速恢复 (必须指定文件的所有者)
chown -R mysqla.mysqla /…/dbname
cp -R /…/daname /…/mysql/data
5使用 LOAD DATA 导入数据
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
注意
恢复./mysqlpump -h xx -P xx -uroot -pxx --default-parallelism=6 -B t1 > test55.sql创建的时 ，必须创建同名数据库，或清空原有数据库,其内包含指定库名
恢复./mysqldump -h xx -P xx -uroot -pxx --no-tablespaces t1 > test5.sql创建的时 ，可指定任意库名，其内不包含指定库名
数据库迁移
同版本数据库 复制数据库文件目录 （仅支持MyISAM类型）
mysqldump将a.com主机数据库dbname迁到b.com
mysqldump -h a.com -u root -p dbname | mysql -h b.com -u root -p
mysqldump将a.com主机全部数据库迁到b.com
mysqldump -h a.com -u root -p --all-databases | mysql -h b.com -u root -p
导入导出
先查看配置：>show variables like '%secure%';
secure_file_prive=null ––限制mysqld 不允许导入导出
secure_file_priv=/path/ – --限制mysqld的导入导出只能发生在默认的/path/目录下
secure_file_priv=’’ – --不对mysqld 的导入 导出做限制
secure_file_prive为只读变量，只能在my.cnf[mysqld]添加secure_file_prive=/xxxx路径。
导出
使用select语句导出 (导出默认位置：/.../mysql/data/库名( 具有写权限))
>use test02;
>select * from test02.testbak into OUTFILE ""aa.txt""; （默认无表头）"
使用mysqldump导出(不指定tables则导出所有表)
格式 mysqldump -T path dbname tables [options] -u root -p
mysqldump -T /test_sql test02 testbak -u root -p （默认无表头）
使用mysql导出（默认包含字段名称，--vertical多行显示 --html格式 --xml格式）
mysql -u root -p --execute="select * from table;" dbname > /test_sql/a.txt（有表头）
mysql -u root -p --vertical --execute="select * from testbak;" test02 > /test_sql/a.txt
mysql -u root -p --html --execute="select * from testbak;" test02 > /test_sql/a.html
mysql -u root -p --xml --execute="select * from testbak;" test02 > /test_sql/a.xml
导入
使用 load data infile 导入文本文件
LOAD DATA INFILE 'file.txt' INTO TABLE db.table [options] [ignore number lines];
>LOAD DATA INFILE '/test_sql/a.txt' INTO TABLE test03.loadfile;
#提前创建test.loadfile库表，并且表头符合a.txt，如果a.txt含表头，需删除表头
使用mysqlimport导入文本文件
格式 mysqlimport -u root -p dbname file.txt [options]
mysqlimport -u root -p test05 /test_sql/a.txt
# 必须提前创建test05.a（table名称与file一致，table表头符合file.txt）
使用sql文件创建表
use db;
source /…/xx.sql; #(source /var/log/mysql/a.sql;)
成功返回 ：Query OK, 0 rows affected，
show tables；查看表情况
批量删除表
先查询表名称，格式化输出
Select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_name LIKE '8%';
# 有重复项，会多删除。注意
再复制输出，运行
