第十二章 mysql优化

引擎：
查看引擎：
mysql> show engines;
mysql> SHOW VARIABLES LIKE '%storage_engine%';
mysql> show create table t1;
mysql> show table status like 't1'; 

临时指定引擎：
mysql> create table innodb1(id int)engine=innodb;
修改默认引擎： 
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB  ----引擎
修改已经存在的表的引擎：
mysql> alter table t2 engine=myisam;

优化：

调优思路：
1.数据库设计与规划--以后再修该很麻烦，估计数据量，使用什么存储引擎
2.数据的应用--怎样取数据，sql语句的优化
3.mysql服务优化--内存的使用，磁盘的使用
4.操作系统的优化--内核、tcp连接数量
5.升级硬件设备

只对innodb引擎
物理分区:
#mkdir /data
#chown mysql.mysql /data
mysql> create table t1(id int,name char(20)) data directory='/data/';----指定存储表的内容目录
表结构存储的文件是.frm结尾的文件。不能指定。
如果创建不成功给data目录增加权限。chown mysql.mysql data

 

mysql服务优化
    mysql> show status         看系统的资源     ---各种状态参数。
    mysql> show variables    看变量，在my.cnf配置文件里定义的
    mysql> show warnings    查看最近一个sql语句产生的错误警告，看其他的需要看.err日志
    mysql> show processlist 显示系统中正在运行的所有进程。
    mysql> show errors

 

字符集设置
    临时：
        mysql> create database db1 CHARACTER SET = utf8;
        mysql> create table t1(id int(10)) CHARACTER SET = utf8;      
        
          5.7/ 5.5版本设置：
            [mysqld]
            character_set_server = utf8
===========================================================================================
慢查询：  
  查看是否设置成功：
    mysql> show variables like '%query%';

 

当连接数的数值过小会经常出现ERROR 1040: Too many connections错误。
这是是查询数据库当前设置的最大连接数
mysql> show variables like '%max_connections%';

这是查看超时时间
mysql> show global variables like '%timeout%';

强制限制mysql资源设置:
vim /etc/my.cnf
max_connections = 1024  并发连接数，根据实际情况设置连接数。
connect_timeout= 5   单位秒 ----超时时间，默认30秒

 

innodb引擎：       
   innodb-buffer-pool-size   //缓存 InnoDB 数据和索引的内存缓冲区的大小
innodb-buffer-pool-size=#   ----值     
        这个值设得越高,访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的 80%。
vim /etc/my.cnf
innodb-buffer-pool-size=2G
wait_timeout=10 终止空闲时间超过10秒的链接，避免长连接
max_connect_errors=10 //10次连接失败就锁定，使用flush hosts 解锁，

mysql> flush hosts;     #解锁 

#锁表
锁定数据表，避免在备份过程中，表被更新
mysql>LOCK TABLES tbl_name READ;
#为表增加一个写锁定：
mysql>LOCK TABLES tbl_name WRITE;

#查询是否锁表
mysql> show open tables where in_use >0;
#解锁
mysql> UNLOCK TABLES;

作业

一主双从（gtid方式）：
在准备一台虚拟机做为slave2，关闭防火墙和selinux
192.168.246.130 #mysql-slave2

 

1.由于刚才测试已经有了数据，需要先将master服务上面的数据备份出来。导入slave2的mysql中。这样才能保证集群中的机器环境一致。
在master操作：
[root@mysql-master ~]# mysqldump -uroot -p'qf123' --set-gtid-purged=OFF test > test.sql
[root@mysql-master ~]# ls
test.sql
[root@mysql-master ~]# scp test.sql 192.168.246.130:/root/   #拷贝到slave2

 

#### 开启 GTID 后的导出导入数据的注意点

> Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events

意思是： 当前数据库实例中开启了 GTID 功能, 在开启有 GTID 功能的数据库实例中, 导出其中任何一个库, 如果没有显示地指定--set-gtid-purged参数, 都会提示这一行信息. 意思是默认情况下, 导出的库中含有 GTID 信息, 如果不想导出包含有 GTID 信息的数据库, 需要显示地添加--set-gtid-purged=OFF参数.

mysqldump -uroot  -p  --set-gtid-purged=OFF   --all-databases > alldb.db

导入数据是就可以相往常一样导入了。

 

slave2操作：
安装mysql5.7
[root@mysql-slave2 ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
安装略...
[root@mysql-slave2 ~]# systemctl start mysqld
[root@mysql-slave2 ~]# systemctl enable mysqld
登陆mysql创建一个test的库
[root@mysql-slave2 ~]# mysql -uroot -p'Qf@12345!'
mysql> create database test;
[root@mysql-slave2 ~]# mysql -uroot -p'Qf@12345!'  test < test.sql #将数据导入。

image.png

 

开始配置slave2
[root@mysql-slave2 ~]# vim /etc/my.cnf
server-id=3    #每台机器的id不一样
gtid_mode = ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
[root@mysql-slave2 ~]# systemctl restart mysqld
[root@mysql-slave2 ~]# mysql -uroot -p'Qf@12345!'
mysql> \e
change master to
master_host='192.168.246.129',
master_user='slave',
master_password='123',
master_auto_position=1;
    -> ;
mysql> start slave;  #将slave启动起来
mysql> show slave status\G  #查看一下状态

image.png

测试：

在master上面在创建一个库：

 

[root@mysql-master ~]# mysql -uroot -p'qf123'
mysql> create database qfedu;
mysql> create table qfedu.t1(id int);
mysql> insert into qfedu.t1 values (1);
mysql>

两台slave

 

image.png

 

image.png

 

主从同步完成！

注意: 在关闭和启动mysql服务的时候按顺序先启动master。