mysql数据备份-主从同步恢复

1、xtrabackup-物理备份

全量备份主库数据：xtrabackup --backup --user=root --password=xxx --port=3306 --target-dir=/comProject/backup/db/full_20220831将full_20220831文件上传到从库服务器上关闭存库服务：service mysqld stop对数据进行“预备”（Prepare）操作，使数据文件完全一致：xtrabackup --prepare --target-dir=/comproject/backup/db/full/复制数据到从库：xtrabackup --copy-back --target-dir=/comproject/full_20220831/修改用户和用户组权限：chown -R mysql:mysql /comproject/db启动数据库：service mysqld start

备注：

壹：xtrabackup备份还原速度极快，备份的是ibd文件。mysql的db文件30GB左右，30分钟以内完成备份还原。

贰：备份还原过程，测试环境和正式环境xtrabackup版本、mysql版本需保持一致。

2.mydumper-逻辑备份

mydumper高性能多线程备份和恢复工具。 -t 代表线程数备份：mydumper -h 127.0.0.1 -u root -p password-P 3306 -G -E -R -B xxdb -o xxdb_bak/ -v 3 -t 4 -k还原：myloader -u root -p password -B xxdb -q 25000 -d  xxdb_bak/ -v 3 -t 4还原：myloader -u root -p password -B xxdb -q 25000 -d  ./xxdb/ -v 3 -t 4 -o

注意：对每张表的表结构和表数据分别进行备份，备份速度快，但是还原速度有点慢，但是比mysqldump的还原方式要快很多。

3.mysqldump-逻辑备份

备份：mysqldump -u 用户名 -pPASSWORD数据库名 > 数据库名.sql还原：mysql -uroot -pPASSWORD 数据库名 < 数据库名.sql

注意：将整个库表结构和表数据进行备份，导出为一个巨大的sql文件，导出速度很快，恢复速度最慢

4、数据库升级

升级前全量备份db.数据库全库备份文件解压：tar xvf mysql-8.0.30-1.el7.x86_64.rpm-bundle.tar停止mysql服务：service mysqld stop备份配置文件：cp /etc/{my.cnf,my.cnf.bak0918}升级：rpm -Uvh mysql-community-*.rpm 查询mysql版本：mysql -V还原配置文件：mv /etc/my.cnf.bak0918 /etc/my.cnf重新加载单位：systemctl daemon-reload重启：systemctl start mysqld.service

5.sql语句在控制台执行方式‍

执行单个sql文件：mysql> source  test.sql批量执行sql文件：cat /folds/*.sql | mysql -u root -p cgdb

6、innodb_force_recovery

innodb_force_recovery=0   表示当需要恢复时执行所有的恢复操作；innodb_force_recovery=1   表示忽略检查到的corrupt页；innodb_force_recovery=2   表示阻止主线程的运行，如主线程需要执行full purge操作，会导致crash；innodb_force_recovery=3   表示不执行事务回滚操作；innodb_force_recovery=4   表示不执行插入缓冲的合并操作；innodb_force_recovery=5   表示不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交；innodb_force_recovery=6   表示不执行前滚的操作，强制重启！

说明：数据库损坏，服务无法启动，在/etc/my.cnf配置文件添加强制启动配置。从0-6尝试，直到服务启动为止。服务启动成功后，注释该配置，重新启动数据库服务。如果启动失败，导出全量数据，重新安装数据库服务，导入数据，恢复数据库数据。

1、mysql数据库主节点mysql -A -uadmin -ppsw -h127.0.0.1 -P 7001select * from backends;2、mysql数据库从节点mysql -A -uroot -ppswshow slave status\G;3、mysql从库恢复（和主库全量同步）全量备份主库数据：xtrabackup --backup --user=root --password=xxx --port=3306 --target-dir=/comProject/backup/db/full_20220831将full_20220831文件上传到从库服务器上关闭存库服务：service mysqld stop对数据进行“预备”（Prepare）操作，使数据文件完全一致：xtrabackup --prepare --target-dir=/comproject/backup/db/full/复制数据到从库：xtrabackup --copy-back --target-dir=/comproject/full_20220831/修改用户和用户组权限：chown -R mysql:mysql /comproject/db启动数据库：service mysqld start 修复从节点的master info ：根据/comproject/backup/db/full_20220831/xtrabackup_binlog_info,获取master_log_file和master_log_pos值change master to master_host='10.68.128.2',master_port=3306,master_user='root',master_password='xxx',master_log_file='mysql-bin.000026',master_log_pos=243;启动从库心跳：start slave;查看从库连接状态：show slave status\G;4、主从恢复后，查看从库心跳状态

查看主从节点同步情况，从节点仍然处于down的状态

执行下述脚本，重启cetus服务，主从恢复正常
从库：

create user if not exists "cetus"@"localhost" identified by "123456";GRANT SELECT, PROCESS, FILE, REFERENCES, INDEX, SHOW DATABASES, SUPER, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'cetus'@'localhost'; revoke INSERT, UPDATE, DELETE, CREATE, DROP on *.* from cetus@'localhost';update user set host='%' where user='cetus';GRANT SELECT, PROCESS, FILE, REFERENCES, INDEX, SHOW DATABASES, SUPER, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'cetus'@'%'; revoke INSERT, UPDATE, DELETE, CREATE, DROP on *.* from cetus@'%';FLUSH PRIVILEGES;主库：create user if not exists "cetus"@"localhost" identified by "123456";GRANT ALL PRIVILEGES ON *.* TO `cetus`@`localhost`;update user set host='%' where user='cetus';GRANT ALL PRIVILEGES ON *.* TO `cetus`@`%`;FLUSH PRIVILEGES;CREATE DATABASE if not exists proxy_heart_beat;USE proxy_heart_beat;CREATE TABLE if not exists tb_heartbeat ( p_id varchar(128) NOT NULL, p_ts timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (p_id) ) ENGINE = InnoDB DEFAULT CHARSET = utf8;

重启cetus服务：service cetus restart

复制主库db到从库备份db文件下面的auto.cnf，重启mysql服务会重新生成auto.cnf重启从库查看主节点信息：show master status;建立主从库连接：change master to master_host='10.68.128.2',master_port=3306,master_user='root',master_password='xxx',master_log_file='mysql-bin.000026',master_log_pos=243;重启从节点：start slave;查看从节点状态：show salve status\G;重启cetus服务：service cetus start查看主从状态：select * from backends;

update命令不能与order by limit配合使用

错误写法：update student1 set status=1 where name in (select name from student2 ORDER BY id limit 3)
正确写法：update student1 set status=1 where name in (select name from student2 where date>‘2022-09-01’)
2.多表关联update
update student1 a ,student2 b set a.status=b.status where a.name=b.name
3.查看个数据库大小

select table_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'from information_schema.tablesgroup by table_schemaorder by sum(data_length) desc, sum(index_length) desc;

查看某个数据库中表大小

select table_schema as '数据库',table_name as '表名',table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)'from information_schema.tableswhere table_schema='xxxdb'order by data_length desc, index_length desc;

创建视图

create view vc_test as (select * from test_table)

substring灵活用法

name列内容为：num3_4selecct * from test_table where substring(a.name, 4, 1) > substring(a.name, 6, 1)

case when用法

selectID,NAME,GENDERcase when GENDER = 1 THEN '男'else '女'end '性别'

数字转换为百分数

concat拼接：select concat(num,'%') from test_tablefloor函数：select concat(floor(0.82*100),'%') from  test_table--82%去掉小数点后两位：truncate（0.889*100,2）四舍五入：round(0.8897*100,2)

表字段可以设置默认值
通过可视化工具设置默认值。
或者通过create语句，设置default值
10.清空表数据
truncate table table_name

数据库操作注意事项：
1、操作库之前先备份db。
2、操作表之前先备份表。

来源地址：https://blog.csdn.net/qq_39787367/article/details/130941810