mysql数据备份并重置-Toy模板网

这篇具有很好参考价值的文章主要介绍了mysql数据备份并重置。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

mysql数据备份并重置

1.备份mysql数据

mysqldump -uroot --single-transaction -R -E --databases lc2 cpm a10_goods self_warehouse > /mnt/vdc1/var/lib/mysql/datadir/lc2_cpm_a10_goods_self_warehouse.sql -p Y6B2wb

2.初始化mysql

mysqld --initialize;

3.修改配置文件

z mysql
cd /etc/mysql/mysql.conf.d
vim mysql.cnf

配置文件如下：

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
#datadir		= /mnt/vdc1/var/lib/mysql/data # 旧数据
datadir		= /mnt/vdc1/var/lib/mysql/my_data # 新数据
log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address	= 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# binlog
#server_id=2
#log-bin=/var/log/mysql/mysql-bin
#expire_logs_days = 3
#binlog_format = ROW
#binlog_do_db = cpm
#binlog_do_db = lc2
# has gone away
max_allowed_packet=100M
wait_timeout=28800

4.分配数据可执行权限

chown mysql:mysql /mnt/vdc1/var/lib/mysql/my_data -R

5.查找mysql重置后的root密码

grep -a generated /var/log/mysql/error.log

6.登录数据库,修改密码

mysql -uroot -p

mysql>
alter user ‘root’@‘localhost’ identified by ‘Y6B2wb’;
flush privileges;

7.创建备份还原的数据库

mysql>
create database mydatabase character set utf8mb4 collate utf8mb4_unicode_ci;
create database cpm character set utf8mb4 collate utf8mb4_unicode_ci;
create database a10_goods character set utf8mb4 collate utf8mb4_unicode_ci;
create database lc2 character set utf8mb4 collate utf8mb4_unicode_ci;
create database self_warehouse character set utf8mb4 collate utf8mb4_unicode_ci;

8.重新给账号分配权限

mysql>
grant select,insert,update,delete on self_warehouse.* to spider@192.31.52.90 identified by ‘U2Fx1’;
grant select,insert,update,delete on self_warehouse.* to tpuser@192.31.52.90 identified by ‘U2Fx1’;
grant select,insert,update,delete on lc2.* to spider@172.31.52.90 identified by ‘U2Fx1’;
grant select,insert,update,delete on lc2.* to tpuser@192.31.52.90 identified by ‘U2Fx1’;
grant select,insert,update,delete on cpm.* to spider@192.31.52.90 identified by ‘U2Fx1’;
grant select,insert,update,delete on cpm.* to tpuser@192.31.52.90 identified by ‘U2Fx1’;
grant select,insert,update,delete on a10_goods.* to spider@192.31.52.90 identified by ‘U2Fx1’;
grant select,insert,update,delete on a10_goods.* to tpuser@192.31.52.90 identified by ‘U2Fx1’;
flush privileges;

9.还原数据

pv /mnt/vdc1/var/lib/mysql/datadir/lc2_cpm_a10_goods_self_warehouse.sql | mysql -uroot --ssl-mode=disabled -p文章来源地址https://www.toymoban.com/news/detail-624585.html

到了这里，关于mysql数据备份并重置的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！