<服务>MySQL字符集和备份

时间：Jan. 3, 2017 分类：Linux服务

MySQL字符集

数据库字符集介绍

字符集是一套文字符号及编码比较规则的集合，计算机第一个字符集为ASC2，MySQL数据库字符集包括字符集和字符集的校对规则，字符集用来定义MySQL字符串的存储方式，校对规则是定义比较字符串的方式。

常见字符集

常见字符集	长度	说明
GBK	2	非国际标准
UTF-8	3	中英文混合字符集
latin1	1	MySQL默认字符集
utf8mb4	4	UTF-8 Unicode，移动互联网常用

字符集选择

如果是处理各种各样的文字最好是选择Unicode字符集，对于MySQL来说就是UTF-8，如果应用处理少量的英文，仅有少量的汉字UTF-8更好。
如果只需要支持中文，并且数据量大，就可以使用 GBK，如果需要大量运算，比较排序等，性能更高。
处理移动互联网业务可能需要使用utf8mb4字符集。

字符集含义

mysql> show variables like 'character_set%';
+--------------------------+-----------------------------------------+
| Variable_name            | Value                                   |
+--------------------------+-----------------------------------------+
| character_set_client     | utf8                                    |
| character_set_connection | utf8                                    |
| character_set_database   | latin1                                  |
| character_set_filesystem | binary                                  |
| character_set_results    | utf8                                    |
| character_set_server     | latin1                                  |
| character_set_system     | utf8                                    |
| character_sets_dir       | /usr/local/mysql-5.5.32/share/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)

character_set_client客户端字符集
character_set_connection连接字符集
character_set_database数据库字符集，配置文件指定或建表建表指定
character_set_results返回结果字符集
character_set_server服务器字符集，配置文件指定或建表建表指定

不乱码的4种方式

设置客户端的字符集与表的相同set names latin1
在sql文件指定，在数据前添加set name latin1
通过指定导入参数改变字符集
通过配置文件设置客户端和服务端的字符集mysql -uroot -p --default-character- set=latin1 why < test.sql

思想是linux，客户端，服务端字符集统一，一般我们通过在中英文混合的情况下，建议使用utf8，然后库和表就跟着mysql的字符集即可。

set names 到底做了什么?

haracter_set_client,character_set_connectionhe character_set_results生效为指定字符集。

操作系统字符集

[root@why ~]# cat /etc/sysconfig/i18n
LANG="zh_CN.UTF-8

客户端字符集

客户端字符集一般不配置，Linux系统的字符集一致，通过/etc/my.cnf文件配置,修改配置后不需要重启，即可生效。

[client]
default-character-set=utf8

服务端字符集

服务端可以通过/etc/my.cnf文件配置并重启

[mysqld]
5.1版本default-character-set=utf8
5.5版本character-set-server=utf8

库字符集

创建库指定create database why_utf8 default character set UTF8 collate utf8_general_ci, 可以通过show character set

查看MySQL配置

mysql> show variables；
mysql> show global status；

不重启数据库修改参数

mysql> set global key_buffer_size = 8192;
mysql> show variables like 'key_buffer_size';
并且修改配置文件 my.cnf

不过对于一些开关类的参数，这样修改是不生效的，必须通过修改配置文件重启数据库。

修改线上MySQL数据库字符集

导出表结构

mysqldump -uroot -p --default-character-set=latin1 -d why > table.sql

-d 只导出表结构
这个过程没有指定-B，加-B会比不加-B参数导出的时候多一个创建库的语句和use该库的语句
修改表结构

table.sql 中将latin1改为utf8

导出数据

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 why > data.sql

--quick 用于转储大的表，强制mysqldump从服务器一次一行的检索数据而不是检索所有行并输出前CACHE到内存中。
--no-create-info 不创建CREATE TABLE语句
--extended-insert 使用包括几个VALUES列表的多行INSERT语法，这样做的好处是文件小，IO也小
--default-character-set=latin1 按照原有字符集导出数据，这样导出的数据不会有乱码
修改数据sql中的字符集

建库

create database why default charset utf8;

创建表

mysqldump -uroot -p why < table.sql

导入数据

mysqldump -uroot -p why < data.sql

数据库备份

这种数据备份为一种逻辑备份，把数据和库通过sql语句的方式进行导出

[root@why ~]# mysqldump -uroot -p -B why_utf8 > /opt/why_utf8_bak.sql
Enter password: 
[root@why ~]# ll !$
ll /opt/why_utf8_bak.sql
-rw-r--r-- 1 root root 3621 12月 25 23:28 /opt/why_utf8_bak.sql
[root@why ~]# egrep -v "#|\/|^$|--" /opt/why_utf8_bak.sql
USE `why_utf8`;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_index_age` (`age`),
  KEY `index_name` (`name`),
  KEY `index_dept` (`dept`(8)),
  KEY `index_name_dept` (`name`,`dept`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'why',12,'dba'),(2,'mabiao',13,'dev'),(3,'yanwei',15,'dev'),(4,'liuyiping',16,'ccie'),(5,'chenyu',11,'ops');
UNLOCK TABLES;

错误的使用方式

[root@why ~]# mysqldump -uroot -p -A -B why_utf8 > /opt/why_utf8_bak.sql
[root@why ~]# egrep -v "#|\/|^$|--" /opt/why_utf8_bak.sql
Usage: mysqldump [OPTIONS] database [tables]

这是因为-A为指定所有库，而-B为指定库，两个参数冲突没有备份到数据，这个过程也没有提示输入密码。

数据恢复

[root@why ~]# mysql -uroot -p  why_utf8 < /opt/why_utf8_bak.sql

MySQL增量恢复数据

恢复数据也只是备份时间点的数据，但是在备份时间到恢复数据的时间之内的数据就没有了，这时就要通过log—bin来增量恢复。

前提条件：开启log—bin

[root@why ~]# vi /etc/my.cnf
#log-bin=mysql-bin修改为log-bin=mysql-bin
重启mysql
[root@why ~]# /etc/init.d/mysqld restart;
Shutting down MySQL...                                     [确定]
Starting MySQL...                                          [确定]

mysql> update student set name='chenyu' where id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@why data]# cd /usr/local/mysql/data/
[root@why data]# ll
总用量 28720
-rw-rw---- 1 mysql mysql 18874368 12月 26 01:29 ibdata1
-rw-rw---- 1 mysql mysql  5242880 12月 26 01:29 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 12月 25 06:27 ib_logfile1
drwx------ 2 mysql root      4096 12月 25 06:24 mysql
-rw-rw---- 1 mysql mysql      316 12月 26 01:29 mysql-bin.000001
-rw-rw---- 1 mysql mysql       19 12月 26 01:26 mysql-bin.index
drwx------ 2 mysql mysql     4096 12月 25 06:24 performance_schema
drwx------ 2 mysql mysql     4096 12月 25 17:49 why
-rw-r----- 1 mysql root     14124 12月 26 01:26 why.err
drwx------ 2 mysql mysql     4096 12月 25 17:56 why_gbk
-rw-rw---- 1 mysql mysql        5 12月 26 01:26 why.pid
drwx------ 2 mysql mysql     4096 12月 26 01:26 why_utf8
[root@why data]# mysqlbinlog mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#161226  1:26:24 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.32-log created 161226  1:26:24 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
QAFgWA8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABAAWBYEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#161226  1:29:14 server id 1  end_log_pos 179   Query   thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1482686954/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 179
#161226  1:29:14 server id 1  end_log_pos 289   Query   thread_id=1 exec_time=0 error_code=0
use `why_utf8`/*!*/;
SET TIMESTAMP=1482686954/*!*/;
update student set name='chenyu' where id=5
/*!*/;
# at 289
#161226  1:29:14 server id 1  end_log_pos 316   Xid = 6
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

模拟数据增加：再增加一条数据

mysql> insert into student values(6,'anxiang',20,'nothing');
Query OK, 1 row affected (0.01 sec)

切分日志

[root@why data]# mysqladmin -uroot -p123456 flush-log
[root@why data]# ll
总用量 28724
-rw-rw---- 1 mysql mysql 18874368 12月 26 01:38 ibdata1
-rw-rw---- 1 mysql mysql  5242880 12月 26 01:38 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 12月 25 06:27 ib_logfile1
drwx------ 2 mysql root      4096 12月 25 06:24 mysql
-rw-rw---- 1 mysql mysql      577 12月 26 01:40 mysql-bin.000001
-rw-rw---- 1 mysql mysql      107 12月 26 01:40 mysql-bin.000002
-rw-rw---- 1 mysql mysql       38 12月 26 01:40 mysql-bin.index
drwx------ 2 mysql mysql     4096 12月 25 06:24 performance_schema
drwx------ 2 mysql mysql     4096 12月 25 17:49 why
-rw-r----- 1 mysql root     14124 12月 26 01:26 why.err
drwx------ 2 mysql mysql     4096 12月 25 17:56 why_gbk
-rw-rw---- 1 mysql mysql        5 12月 26 01:26 why.pid
drwx------ 2 mysql mysql     4096 12月 26 01:26 why_utf8

导入备份数据

[root@why data]# mysql -uroot -p123456 why_utf8 < /opt/why_utf8_bak.sql 
[root@why data]# mysql -uroot -p123456 -e 'select * from why_utf8.student'
+----+-----------+-----+------+
| id | name      | age | dept |
+----+-----------+-----+------+
|  1 | why       |  12 | dba  |
|  2 | mabiao    |  13 | dev  |
|  3 | yanwei    |  15 | dev  |
|  4 | liuyiping |  16 | ccie |
|  5 | chenyu    |  11 | ops  |
+----+-----------+-----+------+

导入增量数据

[root@why data]# mysqlbinlog -d why_utf8 mysql-bin.000001 | grep -v update > bin.sql
[root@why data]# mysql -uroot -p123456 why_utf8 < bin.sql 
[root@why data]# mysql -uroot -p123456 -e 'select * from why_utf8.student'
+----+-----------+-----+---------+
| id | name      | age | dept    |
+----+-----------+-----+---------+
|  1 | why       |  12 | dba     |
|  2 | mabiao    |  13 | dev     |
|  3 | yanwei    |  15 | dev     |
|  4 | liuyiping |  16 | ccie    |
|  5 | chenyu    |  11 | ops     |
|  6 | anxiang   |  20 | nothing |
+----+-----------+-----+---------+

这样就可以把误更新的数据恢复了。

防止数据误更新

通过-U参数登录

[root@why ~]# mysql -U -uroot -p
Enter password: 

mysql> update  student set dept='dev';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql> update  student set dept='dev' where name = 'chenyu'; 
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

另外可以通过别名的方式进行防止出现整列update，例如alisa mysql='mysql -U'

分库备份

[root@why ~]# mysql -uroot -p123456 -e "show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| why                |
| why_gbk            |
| why_utf8           |
+--------------------+
[root@why ~]# mysql -uroot -p123456 -e "show databases" | egrep -Evi "database|information|performance"
mysql
why
why_gbk
why_utf8
[root@why ~]# mysql -uroot -p123456 -e "show databases" | egrep -Evi "database|information|performance" | sed -r 's?^([a-z].*$)?mysqldump -uroot -p123456 -B \1 | gzip > /opt/\1.sql.gz?g'
mysqldump -uroot -p123456 -B mysql | gzip > /opt/mysql.sql.gz
mysqldump -uroot -p123456 -B why | gzip > /opt/why.sql.gz
mysqldump -uroot -p123456 -B why_gbk | gzip > /opt/why_gbk.sql.gz
mysqldump -uroot -p123456 -B why_utf8 | gzip > /opt/why_utf8.sql.gz
[root@why ~]# mysql -uroot -p123456 -e "show databases" | egrep -Evi "database|information|performance" | sed -r 's?^([a-z].*$)?mysqldump -uroot -p123456 -B \1 | gzip > /opt/\1.sql.gz?g'|bash
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
[root@why ~]# ll /opt/ | grep gz
-rw-r--r--  1 root root 144248 12月 26 08:07 mysql.sql.gz
-rw-r--r--  1 root root    513 12月 26 08:07 why_gbk.sql.gz
-rw-r--r--  1 root root    512 12月 26 08:07 why.sql.gz
-rw-r--r--  1 root root   1252 12月 26 08:07 why_utf8.sql.gz

报警告我们可以通过--events参数有时候出错也许只有一个库，我们只需要恢复一个库。但是备份需要备份所有的库

分表内备份

mysqldump -uroot -p123456 why_utf8 student > why_utf8.student.sql

提供备份脚本


BKPATH=/opt
for db in `mysql -uroot -p123456 -e "show databases" | sed 1d |egrep -v "_schema|mysql"`
do
    for table in ` mysql -uroot -p123456 -e "show tables from $db" | sed 1d`
    do
        mkdir -p $BKPATH/$db
        mysqldump -uroot -p123456 $db $table | gzip > $BKPATH/$db/${table}_$(date +%F).sql.gz
    done
done

MySQL数据库备份与恢复参数

-A 备份所有库
-B db1 db2 备份多个库
-d 备份表结构
-t 备份数据
-F 切割binlog
-X 锁表
-l 锁表只读
--compact 优化导出内容大小，不会包括默认选项中的各种注释使容量更少
--master-data 增加binlog日志文件名机对应的位置点
--single-transaction 适合innodb事务数据备份保持事务逻辑性，工作原理是设定本次回话隔离级别为 REPEATABLE READ，以确保本次回话不会看到别的回话提交的数据。

master-data=1与master-data=2的区别

使用matser-data=2的参数，会产生一个-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=1191;用于区分全备的数据后的bin-log文件和bin-log的备份点，而master-data=1是没有注释，一般用于主从同步。

不同引擎的备份方式

myisam引擎备份

mysqldump -uroot -p123456 -A --master-data=2 -X

innodb引擎备份

mysqldump -uroot -p123456 -A --master-data=2 --single-transaction

详细备份方式

for MyISAM

mysqldump -uroot --all-databases --flush-privileges --lock-all-tables \
--master-data=1 --flush-logs --triggers --routines -events \
--hex-blob |gzip > bak.sql

for InnoDB

mysqldump -uroot --all-databases --flush-privileges --single-transaction \
--master-data=1 --flush-logs --triggers --routines -events \
--hex-blob |gzip > bak.sql

如果不指定-B参数进行备份，就需要在恢复的时候指定库

source恢复数据库

mysql > source bak.sql

注意,如果使用相对路径，就是客户端登录时相对的路径。

常用数据库信息查询

show status;查看当前会话的数据库状态信息
show global status;查看整个数据库运行状态信息
show processlist; 查看正在执行的SQL语句，信息不全面
show full processlist;查看正在执行的SQL语句，信息全面
show variables;查看数据库参数信息

mysqlbinlog

-d 选择库当我们只有一个库数据需要增量恢复，我们如果导出了所有库的bin-log数据进行恢复，当在插入不需要恢复的数据的时候，就会报错，导致恢复失败。

指定位置点恢复

mysqlbinlog mysql-bin.000020 --start-position=365 --stop-position=465 -r 
bak.sql

指定时间点恢复

mysqlbinlog mysql-bin.000020 --start-datetime='2016-12-31 15:15:15' --stop-datetime='2016-12-31 15:15:20' -r bak.sql

会恢复在这一个区间和的所有数据，如果不指定开始时间，就会从开头到指定结束时间，如果不指定结束时间，就是从指定开始时间到结尾。

用时间就会一个很大的问题，在同一时间（1s内）就可能有多条操作记录

火眼征信大数据工程师闫大佬

MySQL字符集

数据库字符集介绍

常见字符集

字符集选择

字符集含义

不乱码的4种方式

set names 到底做了什么?

操作系统字符集

客户端字符集

服务端字符集

库字符集

查看MySQL配置

不重启数据库修改参数

修改线上MySQL数据库字符集

导出表结构

修改表结构

导出数据

修改数据sql中的字符集

建库

创建表

导入数据

数据库备份

数据库备份

错误的使用方式

数据恢复

MySQL增量恢复数据

前提条件：开启log—bin

模拟数据增加：再增加一条数据

切分日志

导入备份数据

导入增量数据

防止数据误更新

分库备份

分表内备份

MySQL数据库备份与恢复参数

master-data=1与master-data=2的区别

不同引擎的备份方式

myisam引擎备份

innodb引擎备份

详细备份方式

source恢复数据库

常用数据库信息查询

mysqlbinlog

指定位置点恢复

指定时间点恢复