1 服务端启动
1.1 mysqld
二进制文件安装可通过此命令启动
mysqld --verbose --help
/root/mysql/mysql-5.7.31-el7-x86_64/bin/mysqld \
--basedir=/root/mysql/mysql-5.7.31-el7-x86_64 \
--datadir=/opt/mysql/data \
--plugin-dir=/root/mysql/mysql-5.7.31-el7-x86_64/lib/plugin \
--user=mysql --log-error=/var/log/mariadb/mariadb.log \
--pid-file=/opt/mysql/data/iz3ic059y15k8z337brsikz.pid \
--socket=/var/lib/mysql/mysql.sock
1.2 mysqld_safe
一般选取此command启动，参数会传递至mysqld
/root/mysql/mysql-5.7.31-el7-x86_64/bin/mysqld_safe \
--basedir=/root/mysql/mysql-5.7.31-el7-x86_64 --datadir=/opt/mysql/data \
-defaults-file=/etc/my.cnf --pid-file=/opt/mysql/data/mysqld_safe.pid &
1.3 mysql.server
脚本启停服务
mysql.server start
mysql.server stop
自启动如下
cp mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql
# 如若linux未有chkconfig，可将启动添加至 /etc/rc.local or /etc/init.d/boot.local
/bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &'
/etc/my.cnf配置文件
[mysqld]
datadir=/usr/local/mysql/var
socket=/var/tmp/mysql.sock
port=3306
user=mysql
secure_file_priv=
[mysql.server]
basedir=/usr/local/mysql
2、客户端应用
2.1 mysqladmin
用来检测服务端配置和状态
shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...
2.2 mysqldump
导出数据，没有多线程。属于逻辑导出，对于大表的应该选择物理导出
mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases
2.3 mysqlimport
导入数据，使用和命令行
LOAD DATA基本一样
mysqlimport [options] db_name textfile1 [textfile2 ...]
2.4 mysqlpump
可多线程数据导出
mysqlpump [options] > dump.sql
mysqlpump [options] --result-file=dump.sql
2.5 mysqlshow
快速查看数据库、表、列和索引信息
mysqlshow [options] [db_name [tbl_name [col_name]]]
2.6 mysqldumpslow
解析/usr/local/mysql/data/mysqld57-slow.log日志，展示格式化慢的sql
mysqldumpslow [options] [log_file ...]
2.7 导入导出
- 创建
create database db1;
use db1;
create table tb1(a int, b int);
insert tb1 values(1,1),(1,2),(1,3),(2,3),(3,3);
create table tb2(a int, b int);
insert tb1 values(2,1),(2,2),(2,3),(3,3),(3,4);
- 导出
# -v 详细信息、-y 是否导出tablespace、-t是否导出表结构
# --single-transaction 为了导出时数据的一致性，生成快照，在事务中进行导出
# --log-error 记录导出时的日志
# --ingore-table 忽略指定表
# -r 导出的文件
mysqldump -h127.0.0.1 -uroot -P3306 -pabc123 -vyt --single-transaction --log-error dump.log --ignore-table db1.tb2 --dump-date -r tb1.sql db1 tb1
# --default-parallelism 多线程设置和--single-transaction互斥
mysqlpump -h127.0.0.1 -uroot -pabc123 -t --no-create-db --default-parallelism 1 --log-error-file pump.log --watch-progress db1 tb1 tb2 > pump.sql
- 导入
LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT}
[, col_name={expr | DEFAULT}] ...]
mysql> load data local infile 'tb1.txt' into table db1.tb1 fields terminated by ','
# --local 从客户端读取文件
# -i 主键冲突保留老的
# --use-threads导入的线程数
mysqlimport -h127.0.0.1 -uroot -pabc123 \
-i --local --fields-terminated-by=',' --use-threads 2 db1 tb1.sql
3、备份恢复
备份分为逻辑备份和物理备份，物理备份取数据库的数据文件下的文件进行压缩输出，备份期间需要手动锁定相关表
3.1 逻辑备份
3.1.1 增量备份
增量备份靠binlog来实现，先做全量备份，每次的ddl操作根据stats或者raw的方式记录在配置的binlog中，对binlog的存储需要考虑到高可用，借助mysqldump和mysqlbinlog工具可以做到具体时间点的恢复，备份时需要获取只读锁
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /opt/mysql/data/binlogs/host-bin |
| log_bin_index | /opt/mysql/data/binlogs/host-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------------+
| 参数 | 取值 | 备注 |
|---|---|---|
| log_bin | on/off | 开启log_bin |
| log_bin_basename | /opt/mysql/data/binlogs | log_bin存放的目录,最好为raid或者高可用的存储上，主要为了防止crash之后，磁盘bin_log数据丢失。 |
| server_id | 1 | 该参数和log_bin一起配置，以便服务启动 |
| –binlog-do-db | db_name | 按照statements或者raw针对那个库进行log_bin的记录 |
| –binlog-ignore-db=name | db_name | 和上述相反 |
| binlog_format | ROW/STATEMENT/MIXED | statement针对的是use库下的对象，raw针对是具体实体表对象 |
| binlog_row_image | full/minimal/noblob | full所有的列/min变换的列/除去blob和text的列 |
| expire_logs_days | 0～99 | 0为关闭 |
| max_binlog_size | 4096b～1G | 单个binlog的文件大小设置 |
| sql_log_bin | ON/OFF | 当前会话是否要记录到binlog中 |
| sync_binlog | 0/1/N | 0完全刷新到磁盘，1事务提交前刷新，n几个binlog后刷新 |
| log_bin_trust_function_creators | OFF/ON | 创建函数的权限管制0，需要赋权，create routine，申明时需要添加DETERMINISTIC关键字 |
-
配置binlog重启mysql
[mysqld] datadir=/opt/mysql/data socket=/var/lib/mysql/mysql.sock symbolic-links=0 secure_file_priv= log_bin=/opt/mysql/data/binlogs/host-bin server_id=1 binlog_format=row binlog_row_image=full expire_logs_days=7 max_binlog_size=536870912 sync_binlog=1 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/opt/mysql/data/mysqld_safe.pid/root/mysql/mysql-5.7.31-el7-x86_64/bin/mysqld_safe -defaults-file=/etc/my.cnf &mysql> show variables like '%log_bin%'; +---------------------------------+----------------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------------+ | log_bin | ON | | log_bin_basename | /opt/mysql/data/binlogs/host-bin | | log_bin_index | /opt/mysql/data/binlogs/host-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+----------------------------------------+ -
基准备份
基准备份是将之前的数据全量备份至backup_base.sql中，此后的更新操作另起binlog做记录
# --single-transaction 获取可读锁 # --flush-logs 刷新ddl至新的binlog作为增量，以此次作为基准 # --master-data输出binlog的信息至备份文件中 mysqldump -pabc123 --single-transaction --flush-logs --master-data=2 \ --all-databases > /root/backup_base.sql -
更新操作
mysql> show binary logs; +-----------------+-----------+ | Log_name | File_size | +-----------------+-----------+ | host-bin.000001 | 200 | | host-bin.000002 | 200 | | host-bin.000003 | 782 | | host-bin.000004 | 951 | | host-bin.000005 | 1040 | | host-bin.000006 | 782 | | host-bin.000007 | 458 | | host-bin.000008 | 844530 | | host-bin.000009 | 154 | +-----------------+-----------+ create database db1; create table db1.tb1 (a int); insert into db1.tb1 values(1),(2); mysqldump -pabc123 --single-transaction --flush-logs --master-data=2 \ --all-databases > /root/backup_base.sql create database db2; create table db2.tb2(a int); insert into db2.tb2 values(10); mysql> show binary logs; +-----------------+-----------+ | Log_name | File_size | +-----------------+-----------+ | host-bin.000001 | 200 | | host-bin.000002 | 200 | | host-bin.000003 | 782 | | host-bin.000004 | 951 | | host-bin.000005 | 1040 | | host-bin.000006 | 782 | | host-bin.000007 | 458 | | host-bin.000008 | 844530 | | host-bin.000009 | 772 | | host-bin.000010 | 720 | +-----------------+-----------+ flush logs; mysql > show binary logs; drop database db1; drop database db2; mysql> show binary logs; +-----------------+-----------+ | Log_name | File_size | +-----------------+-----------+ | host-bin.000001 | 200 | | host-bin.000002 | 200 | | host-bin.000003 | 782 | | host-bin.000004 | 951 | | host-bin.000005 | 1040 | | host-bin.000006 | 782 | | host-bin.000007 | 458 | | host-bin.000008 | 844530 | | host-bin.000009 | 772 | | host-bin.000010 | 766 | | host-bin.000011 | 462 | +-----------------+-----------+ -
恢复
# 1）基准恢复，恢复db1的基准数据 mysql -pabc123 < /root/backup_base.sql grep -A 3 'Position to start replication or point-in-time recovery from' backup_base.sql | grep -v "^$" # 2）查看起始binlog的标记 -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='host-bin.000010', MASTER_LOG_POS=154; # 2）binglog的增量恢复，恢复db2更新的数据 mysqlbinlog host-bin.000010 | mysql -pabc123
3.1.2 point-in-time恢复
当产生误操作时，恢复至误操作前的某个标志位，比如删除表的误操作需要恢复，之后的数据也得从在
-
重置logbin操作
--语法 mysql> \h purge binary logs --PURGE BINARY LOGS TO 'mysql-bin.010'; purge binary logs before '2021-02-04 00:00:00'; reset master; mysql> show binary logs; +-----------------+-----------+ | Log_name | File_size | +-----------------+-----------+ | host-bin.000001 | 154 | +-----------------+-----------+ -
创建测试数据
create database db1; use db1; create table tb1(a int, b int); create table tb2(a int); insert into tb1 values(1,10),(2,20),(3,30),(4,40); insert into tb2 values(100),(200); --后期需要恢复tb2 drop table tb2; -
现恢复数据
查询标记位
mysql> \h show binlog event SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] mysql> show binlog events in 'host-bin.000001'; +-----------------+------+----------------+-----------+-------------+-------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-----------------+------+----------------+-----------+-------------+-------------------------------------------------------+ | host-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 | | host-bin.000001 | 123 | Previous_gtids | 1 | 154 | | | host-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | host-bin.000001 | 219 | Query | 1 | 310 | create database db1 | | host-bin.000001 | 310 | Anonymous_Gtid | 1 | 375 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | host-bin.000001 | 375 | Query | 1 | 477 | use `db1`; create table tb1(a int, b int) | | host-bin.000001 | 477 | Anonymous_Gtid | 1 | 542 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | host-bin.000001 | 542 | Query | 1 | 637 | use `db1`; create table tb2(a int) | | host-bin.000001 | 637 | Anonymous_Gtid | 1 | 702 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | host-bin.000001 | 702 | Query | 1 | 773 | BEGIN | | host-bin.000001 | 773 | Table_map | 1 | 819 | table_id: 120 (db1.tb1) | | host-bin.000001 | 819 | Write_rows | 1 | 890 | table_id: 120 flags: STMT_END_F | | host-bin.000001 | 890 | Xid | 1 | 921 | COMMIT /* xid=156 */ | | host-bin.000001 | 921 | Anonymous_Gtid | 1 | 986 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | host-bin.000001 | 986 | Query | 1 | 1057 | BEGIN | | host-bin.000001 | 1057 | Table_map | 1 | 1102 | table_id: 121 (db1.tb2) | | host-bin.000001 | 1102 | Write_rows | 1 | 1147 | table_id: 121 flags: STMT_END_F | | host-bin.000001 | 1147 | Xid | 1 | 1178 | COMMIT /* xid=157 */ | | host-bin.000001 | 1178 | Anonymous_Gtid | 1 | 1243 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | host-bin.000001 | 1243 | Query | 1 | 1357 | use `db1`; DROP TABLE `tb2` /* generated by server */ | | host-bin.000001 | 1357 | Anonymous_Gtid | 1 | 1422 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | host-bin.000001 | 1422 | Query | 1 | 1493 | BEGIN | | host-bin.000001 | 1493 | Table_map | 1 | 1539 | table_id: 120 (db1.tb1) | | host-bin.000001 | 1539 | Update_rows | 1 | 1647 | table_id: 120 flags: STMT_END_F | | host-bin.000001 | 1647 | Xid | 1 | 1678 | COMMIT /* xid=159 */ | | host-bin.000001 | 1678 | Rotate | 1 | 1724 | host-bin.000002;pos=4 | +-----------------+------+----------------+-----------+-------------+-------------------------------------------------------+恢复至删除表之前的数据
# 将期间的更新数据拷贝出来，和后续的操作隔离开 cp host-bin.000001 /opt/mysql/ # 将重建日志刷新至新的binlog中 mysql -uroot -pabc124 -e "flush logs" # 创建tb2的起始为542，从542开始到637建表 mysqlbinlog --start-position 542 --stop-position=637 /opt/mysql/host-bin.000001 | mysql -u root -p # 插入数据 mysqlbinlog --start-position 986 --stop-position=1178 /opt/mysql/host-bin.000001 | mysql -u root -p # 查看tb2的数据是否恢复 mysql> select * from db1.tb2; +------+ | a | +------+ | 100 | | 200 | +------+ 2 rows in set (0.00 sec) # 查看其他的表操作是否正常
3.2 物理备份
待记
扫一扫
138
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
