<服务>MySQL字符集和备份

时间:Jan. 3, 2017 分类:

目录:

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种方式

  1. 设置客户端的字符集与表的相同set names latin1
  2. 在sql文件指定,在数据前添加set name latin1
  3. 通过指定导入参数改变字符集
  4. 通过配置文件设置客户端和服务端的字符集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内)就可能有多条操作记录