mysql修改字符集和备份

修改字符集

背景：公司业务数据book,由于之前建表没注意字符集的问题，导致之前写入的数据出现乱码。现在要将之前的数据和现在数据的字符集一致，不出现乱码情况，将字符集为latin1已有记录的数据转成utf8，并且已经存在的记录不乱码。
步骤
1：导出表结构，sed批量修改为utf8
2：导出之前所有的数据，sed批量修改为utf8
3：删除原有的库表及数据
4：导入表结构和所有数据

一、准备实验环境

1、确保你的数据库准备设置的默认字符集utf8
修改/etc/my.cnf配置文件中character-set-server=utf8，重启mysql
[root@cong11 ~]# vim /etc/my.cnf
character-set-server=utf8
[root@cong11 ~]# systemctl restart mysqld
2、准备默认字符集是latin1的表文件
由于我们books表的字符集是utf8，导入字符集为latin1的sql文件book.sql
上传book<book_latin1.sql文件
[root@cong11 ~]# mysql -uroot -p123456 book<book_latin1.sql
查看books表的字符集
[root@cong11 ~]# mysql -uroot -p123456 -e “show create table book.books;”
查看表内容
[root@cong11 ~]# mysql -uroot -p123456 -e “select * from book.books;”
除了英文和时间，中文都是乱码

二、 导出数据表结构和数据

1、 导出数据表结构
[root@cong11 ~]# mysqldump -uroot -p123456 --default-character-set=latin1 -d book> booktable.sql
编辑booktable.sql 将latin1修改成utf8 （:%s/latin1/utf8/g）
[root@cong11 ~]# vim booktable.sql
2、确保数据库不再更新，导出所有数据
[root@cong11 ~]# mysqldump -uroot -p123456 --quick --no-create-info --extended-insert --default-character-set=latin1 book>bookdata.sql
编辑bookdata.sql 将latin1修改成utf8（:%s/latin1/utf8/g）
[root@cong11 ~]# vim bookdata.sql
参数说明：
–quick：用于大的表
–no-create-info：不导出创建语句(2,‘lisi’)
–extended-insert：使用同时插入多行语句的命令；
–default-character-set=latin1：按原有字符集导出数据；
3、新建book2库
[root@cong11 ~]# mysql -uroot -p123456 -e “create database book2 default charset utf8;”
4、导入我们之前导出的表结构
[root@cong11 ~]# mysql -uroot -p123456 book2 <booktable.sql
5、导入数据
[root@cong11 ~]# mysql -uroot -p123456 book2 <bookdata.sql
6、 查看结果
[root@cong11 ~]# mysql -uroot -p123456 -e"show create table book2.books;"
查看表内容
[root@cong11 ~]# mysql -uroot -p123456 -e"select * from book2.books;"

MySQL备份恢复

一、按照备份时对数据库的影响范围分为以下三种：

1、热备(Hot backup)：在数据库运行中直接备份，对正在运行的数据库没有任何影响。
2、冷备(Cold Backup)：在数据库停止的情况下进行备份
3、温备(Warm Backup)：备份同样在数据库运行时进行，但是会对当前数据库的操作有所影响，例如加一个全局读锁以保证备份数据的一致性（锁表）

二、MySQL备份的类型

1、逻辑备份
mysqldump和SELECT * INTO OUTFILE的方法，一般适用于数据库的升级和迁移，恢复时间较长。是温备份
2、裸文件备份
拷贝数据库的物理文件，数据库既可以处于运行状态，恢复时间较短。是热备份

逻辑备份工具mysqldump

MySQL所提供的命令行工具mysqldump进行备份恢复，数据量太大的时候不推荐使用。
1、导出数据库：mysqldump -u root -p123456 HA>HA.sql
导入数据库前须创建一个空数据库,因为导出时没有将数据库也导出：
方法一：mysql -e ‘create database HA2’ -uroot -p123456
mysql -uroot -p123456 HA2 < HA.sql
方法二: mysql> create database HA2;
mysql> use HA2;
mysql> source /root/HA.sql
2、导出整个库：mysqldump -u root -p123456 -B HA>HA.bak
导出整个库：-B 导出整个库 -A 导出全部数据库
导入整个库
方法一：mysql -uroot -p123456 HA2<HA.bak
方法二：mysql -uroot -p123456
mysql>source /root/HA.bak
3、导出单张表:mysqldump -uroot -p123456 book books >books.sql
4、导出表结构: mysqldump -uroot -p123456 -d book>booktable.sql
5、只导出表中数据: mysqldump -uroot -p123456 -t book>bookdata.sql

xtrbackup热备份工具

1、xtrabackup简介
我们知道，针对InnoDB存储引擎，MySQL本身没有提供合适的热备工具，ibbackup虽是一款高效的首选热备方式，但它是是收费的。好在Percona公司给大家提供了一个开源、免费的Xtrabackup热备工具，它可实现ibbackup的所有功能，并且还扩展支持真正的增量备份功能，是商业备份工具InnoDB Hotbackup的一个很好的替代品。
Xtrabackup包括两个主要工具：Xtrabackup和innobackupex：
2、xtrabackup和innodbbackupex的区别：Xtrabackup只能备份InnoDB和XtraDB两种引擎表，不能备份MyISAM数据表。innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表。Xtrabackup做备份的时候不能备份表结构、触发器等等，智能区分.idb数据文件。另外innobackupex还不能完全支持增量备份，需要和xtrabackup结合起来实现全备
3、四种备份类型
(1)完全备份：对数据库完整的备份
(2)差异备份：在上一次完全备份基础上，对更新的数据进行备份
(3)增量备份：在上次备份的基础上，对更新的数据进行备份
(4)日志备份：二进制日志备份，主从复制
4、实验环境准备好
（mysql5.7.20需安装最新版XtraBackup2.4.9）
(1)上传xtarabackup包，解压。
[root@cong11 ~]# ls
anaconda-ks.cfg boost_1_59_0.tar.bz2 mysql-community-5.7.26-1.el7.src.rpm book_utf8.sql mysql-5.7.26 Percona-XtraBackup-2.4.14-ref675d4-el7-x86_64-bundle.tar
网上下载地址
https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.14-ref675d4-el7-x86_64-bundle.tar
解压
[root@cong11 ~]# tar -xf Percona-XtraBackup-2.4.14-ref675d4-el7-x86_64-bundle.tar
解决依赖，配置好本地yum源
yum install -y percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm
(2)解决libev依赖包
[root@cong11 ~]# yum -y install percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm
报错：需要libev.so.4()依赖
解决：
上传libev-4.03-3.el6.x86_64.rpm包
或者
网络下载地址：http://rpmfind.net/linux/epel/6/x86_64/Packages/l/libev-4.03-3.el6.x86_64.rpm
[root@cong11 ~]# rpm -ivh libev-4.03-3.el6.x86_64.rpm
[root@cong11 ~]# yum install -y percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm
(3) 修改数据目录
注意：安装完成以后记得更改你的/etc/my.cnf配置文件指定数据目录，因为Xtrabackup是根据你的/etc/my.cnf配置文件来获取你备份的文件
[root@cong11 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql/data
[root@cong11 ~]# systemctl restart mysqld #重启msyql
5、权限和链接
xtrabackup需要连接到数据库和datadir操作权限。
xtrabackup或者innobackupex在使用过程中涉及到2类用户权限：
(1)系统用户，用来调用innobackupex或者xtrabackup
(2)数据库用户，数据库内使用的用户
可以单独创建用来备份数据库的用户，安全，并赋予对应的权限

xtrbackup备份和恢复

1、创建全备：
[root@cong11 ~]# mkdir /db_backup
[root@cong11 ~]# innobackupex --user=root --password=123456 /db_backup/ --socket=/usr/local/mysql/mysql.sock
2、插入一些数据到表里面
mysql>create database HA;
mysql> use HA;
mysql>create table students (id int,name char(10),age (int));
mysql> insert into students values(1,‘zheng’,20);
mysql> select * from students;
3、使用-incremental创建增量备份
[root@cong11 ~]# mkdir /zeng
[root@cong11 ~]# innobackupex --user=root --password=123456 --incremental /zeng --incremental-basedir=/db_backup/2019-05-21_12-21-44/ --socket=/usr/local/mysql/mysql.sock
或者使用LSN<日志序列号>来做备份
[root@cong11 ~]# innobackupex --user=root --password=123456 --incremental /zeng/ --incremental-lsn=2622668
#指定上次备份的LSN序号或者上次完整备份的LSN序号
4、查看全备增备的LSN序列号是否对应
[root@cong11 ~]# cat /db_backup/2019-05-21_12-21-44/xtrabackup_checkpoints
[root@cong11 ~]# cat /zeng/2019-05-21_15-26-55/xtrabackup_checkpoints
恢复：
1、停止mysql,删除数据
[root@cong11 ~]# systemctl stop mysqld
[root@cong11 ~]# rm -rf /data/mysql/data
2、修改全备的文件类型（每次恢复增备，就要改一次文件类型）
[root@cong11 ~]# innobackupex --apply-log --redo-only /db_backup/2019-05-21_12-21-44
3、将增备文件放到全备目录下
[root@cong11~]#innobackupex --apply-log /db_backup/2019-05-21_12-21-44 --incremental-dir=/zeng/2019-05-21_15-26-55/
4、恢复全部数据
注：innobackupex --copy-back不会覆盖已存在的文件，还要注意，还原时需要先关闭服务，如果服务是启动的，那么就不能还原到datadir。
[root@cong11 ~]# innobackupex --copy-back /db_backup/2019-05-21_12-21-44/
4、恢复mysql权限
[root@cong11 ~]# chown -R mysql:mysql /data/mysql/data
5、启动mysql
[root@cong11 ~]# systemctl restart mysqld
6、测试数据，查看所有数据是否恢复
[root@cong11 ~]# mysql -uroot -p123456 -e"select * from HA.students;"
https://blog.youkuaiyun.com/xun_jia/article/details/79984682