innobackex工具备份mysql数据 mysql5.7root密码更改 mysql调优 mysql字符集调整-优快云博客

mysql5.7root有默认密码，必须重设密码后，才能进行mysql的操作，以下是设置root密码的步骤：

一、查看默认密码

[root@localhost src]# cat /root/.mysql_secret

# The random password set for the root userat Fri Jan 10 20:00:34 2014 (local time): aJqZsA2m

这里的aJqZsA2m就是生成的root随机密码啦

二、登录mysql

[root@localhost src]# mysql -u root -p

Enter password:

输入上面的密码aJqZsA2m登录，如果你没有把mysql的路径加到path里，那就用绝对路径，mysql -u root -p还可以写成mysql -uroot -paJqZsA2m

三、更改密码

mysql> SET PASSWORD  FOR 'root'@localhost = PASSWORD（'123456'）；
Query OK, 0 rows affected (0.17 sec)

至此，就成功地修改了密码。

MySQL调优

MySQL调优可以从几个方面来做：
1. 架构层：
做从库，实现读写分离；

2.系统层次：
增加内存；
给磁盘做raid0或者raid5以增加磁盘的读写速度；
可以重新挂载磁盘，并加上noatime参数，这样可以减少磁盘的i/o;

3. MySQL本身调优：
(1) 如果未配置主从同步，可以把bin-log功能关闭，减少磁盘i/o
(2) 在my.cnf中加上skip-name-resolve,这样可以避免由于解析主机名延迟造成mysql执行慢
(3) 调整几个关键的buffer和cache。调整的依据，主要根据数据库的状态来调试。如何调优可以参考5.

4. 应用层次：
查看慢查询日志，根据慢查询日志优化程序中的SQL语句，比如增加索引

5. 调整几个关键的buffer和cache

1) key_buffer_size 首先可以根据系统的内存大小设定它，大概的一个参考值：1G以下内存设定128M；2G/256M; 4G/384M;8G/1024M；16G/2048M.这个值可以通过检查状态值Key_read_requests和 Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。注意：该参数值设置的过大反而会是服务器整体效率降低!

2) table_open_cache 打开一个表的时候，会临时把表里面的数据放到这部分内存中，一般设置成1024就够了，它的大小我们可以通过这样的方法来衡量：如果你发现 open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

3) sort_buffer_size 查询排序时所能使用的缓冲区大小,该参数对应的分配内存是每连接独占!如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 4 = 400MB。所以，对于内存在4GB左右的服务器推荐设置为4-8M。

4) read_buffer_size 读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!

5) join_buffer_size 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!

6) myisam_sort_buffer_size 这个缓冲区主要用于修复表过程中排序索引使用的内存或者是建立索引时排序索引用到的内存大小，一般4G内存给64M即可。

7) query_cache_size MySQL查询操作缓冲区的大小，通过以下做法调整：SHOW STATUS LIKE ‘Qcache%’; 如果Qcache_lowmem_prunes该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。如果该值非常大，则表明经常出现缓冲不够的情况，需要增加缓存大小;Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，我们可以根据实际情况做出调整。一般情况下4G内存设置64M足够了。

8) thread_cache_size 表示可以重新利用保存在缓存中线程的数，参考如下值：1G —> 8 2G —> 16 3G —> 32 >3G —> 64
除此之外，还有几个比较关键的参数：

9) thread_concurrency 这个值设置为cpu核数的2倍即可

10) wait_timeout 表示空闲的连接超时时间，默认是28800s，这个参数是和interactive_timeout一起使用的，也就是说要想让wait_timeout 生效，必须同时设置interactive_timeout，建议他们两个都设置为10

11) max_connect_errors 是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。与性能并无太大关系。为了避免一些错误我们一般都设置比较大，比如说10000

12) max_connections 最大的连接数，根据业务请求量适当调整，设置500足够

13) max_user_connections 是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。通常我们设置为100足够

mysql字符集调整

mysql编译安装时，指定字符集的方法：

  mysql编译安装时，指定字符集的方法：
./configure  --with-charset=utf8

mysql的字符集有4个级别的默认设置：服务器级、数据库级、表级和字段级。分别在不同的地方设置，作用也不相同。

1、服务器字符集设定

在mysql服务启动的时候确定，可以在my.cnf中设置：

[mysql]
### 默认字符集为utf8
default-character-set=utf8
[mysqld]
### 默认字符集为utf8
default-character-set=utf8
### （设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行）
init_connect='SET NAMES utf8'

可以用show variables like 'char%';命令查询当前服务器的字符集和校对规则。

mysql>show variables like 'char%';

+--------------------------+----------------------------+

　　| Variable_name | Value |

　　+--------------------------+----------------------------+

　　| character_set_client | utf8 |

　　| character_set_connection | utf8 |

　　| character_set_database | utf8 |

　　| character_set_filesystem | binary |

　　| character_set_results | utf8 |

　　| character_set_server | utf8 |

　　| character_set_system | utf8 |

　　| character_sets_dir | /usr/share/mysql/charsets/ |

　　+--------------------------+----------------------------+

如果增加default-character-set=utf8后，MYSQL启动报错。可以用character_set_server=utf8来取代default-character-set=utf8，就能正常启动了。这是因为MYSQL不同版本识别的问题。

2、数据库级

#创建数据库时指定字符集
mysql>CREATE DATABASE my_db default charset utf8 COLLATE utf8_general_ci;
#注意后面这句话 "COLLATE utf8_general_ci",大致意思是在排序时根据utf8编码格式来排序
#如果指定了数据库编码，那么在这个数据库下创建的所有数据表的默认字符集都会是utf8了

修改MYSQL数据库编码，如果是MYSQL数据库编码不正确，可以在MYSQL执行如下命令:

ALTER DATABASE my_db DEFAULT CHARACTER SET utf8;

以上命令就是将MYSQL的my_db数据库的编码设为utf8

3.表级

#创建表时指定字符集
mysql>create table my_table (name varchar(20) not null default '')type=myisam default charset utf8;
#这句话就是创建一个表,指定默认字符集为utf8
修改MYSQL表的编码：
ALTER TABLE my_table DEFAULT CHARACTER SET utf8; 
#以上命令就是将一个表my_table的编码改为utf8

4、字段级

alter table test add column address varchar(110) after stu_id;
#在stu_id后增加一个字段address

alter table test add id int unsigned not Null auto_increment primary key;
#修改字段的编码：
ALTER TABLE `test` CHANGE `name` `name` VARCHAR( 45 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL 
#以上命令就是将MYSQL数据库test表中name的字段编码改为utf8

innobackex工具备份mysql数据

xtrbackup只能用于备份innodb引擎的数据库，而innobackex 既可以备份innodb引擎的数据库，也可以备份myisam引擎的数据库。备份时也可分为全量备份和增量备份

安装innobackex

安装yum拓展源percona-release


rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

安装percona-xtrabackup

yum install percona-xtrabackup

全量备份mysql

创建并授权备份用户,我们可以直接授权all权限，但是不符合安全原则


mysql  -uroot  -pallen
> grant reload,lock tables,replication client on *.* to 'backupuser'@'localhost'  identified  by  'allen';
> flush  privileges;  #权限为reload,lock tables,replication client

#创建备份保存目录
mkdir  /data/backup

#备份mysql

innobackupex   --defaults-file=/etc/my.cnf  --user=backupuser  --password='allen'    -S /tmp/mysql.sock  /data/backup #–defaults-file=/etc/my.cnf指定配置文件位置是为了获得datadir位置

#备份完成后，会在指定的保存目录中生成一个时间戳目录，该时间戳目录名称也是恢复时的apply-log。

全量备份恢复

停止mysql服务

/etc/init.d/mysqld  stop
ps aux |grep "mysqld"
#不允许mysql进程存在

#删除mysql原有数据
mv /data/mysql  /data/mysql.bak
mkdir /data/mysql

#恢复mysql
innobackupex  --use-memory=512M  --apply-log  /备份的时间戳目录/
# innobackupex  --use-memory=512M  --apply-log  /data/backup/2018-12-21_10-24-06/
#–use-memory=512M指定备份时使用的内存为512M，注意单位。默认为字节


#初始化完成后，进行恢复
innobackupex  --defaults-file=/etc/my.cnf  --copy-back  /备份的时间戳目录/
#innobackupex  --defaults-file=/etc/my.cnf  --copy-back   /data/backup/2018-12-21_10-24-06/
#使用–copy-back参数恢复


#设置权限

chown   -R  mysql:mysql  /data/mysql

增量备份

一段时间后重新全量备份的话，需要耗费的资源较多，这时我们就可以使用增量备份了。

增量备份是基于全量备份的，所以在增量备份操作之前我们需要先进行全量备份

进行数据库操作

#创建测试库test1_backup
mysql -uroot -pallen  -e 'create database test1_backup;'
#导入数据
mysql -uroot -pallen test1_backup  < /tmp/1.sql

模拟数据库数据发生改变，进行增量备份

innobackupex  --defaults-file=/etc/my.cnf  --user=backupuser  --password='allen'  -S /tmp/mysql.sock  --incremental  /data/backup  --incremental-basedir=/data/backup/全量备份时间戳目录/

–incremental表示增量备份，–incremental-basedir指定全量备份时间戳目录，因为本次增量备份是基于全量备份。

转载于:https://my.oschina.net/u/3993226/blog/2991787