Mysql服务器企业实战

1、MySQL简介

MySQL属于传统的关系型数据库产品，其开放式的架构使得用户的选择性很强，而且随着技术的逐渐成熟，MySQL支持的功能也越来越多，性能也在不断地提高，对平台的支持也在增多，此外，社区的开发与维护人数也很多。当下，MySQL因为其功能稳定、性能卓越，且在遵守GPL协议的前提下，可以免费使用与修改，因此深受用户喜爱。

我们知道，关系型数据库的特点是将数据保存在不同的表中，再将这些表放入不同的数据库中，而不是将所有的数据统一放在一个大仓库里，这样的设计加快了MySQL的读取速度，而且它的灵活性和可管理性也得到了很大的提高。访问及管理MySQL数据库的最常用标准化语言为SQL——结构化查询语言。SQL使得对数据库进行存储、更新和存取信息的操作变得更加容易。例如，你能用SQL为一个网站检索产品信息及存储用户信息、博文、帖子等，有关SQL的知识后文会详细讲解。

2、　MariaDB数据库的诞生背景介绍

自甲骨文公司收购MySQL之后，MySQL在商业数据库与开源数据库领域的市场占有份额都跃居第一，这样的格局引起了部分业内人士的担忧，因为商业数据库的老大有可能将MySQL闭源，为了避免Oracle将MySQL闭源，而无开源的类MySQL数据库可用，MySQL社区采用了分支的方式——MariaDB数据库就这样诞生了，MariaDB是一个向后兼容的数据库产品，可能会在以后替代MySQL，其官方地址为https://mariadb.org数据库，我们centos7已经已经默认安装mariadb数据库了，所以我们在以下课程中就直接采用mariadb数据库，mariadb数据库与mysql的用法以及命令完全相同。

3、mariadb安装

我们可以采用两种方式，第一种为rpm命令安装，第二种采用yum命令安装

Rpm命令就是先从镜像网站下载对应的安装包然后采用rpm -ivh命令进行安装即可。

我们采用yum方式安装：

yum install mariadb-server mariadb-client -y

4、启动mariadb数据库以及关闭数据库

启动数据库：systemctl start mariadb

查看数据库服务是否启动 ps -ef|grep mysql

关闭数据库: systemctl stop mariadb

5、进入数据库

我们连接上数据库的命令为 mysql

但设置完数据库密码之后，连数据库时需要提供账户名与密码，格式如下

mysql -u username -p password

6、数据库语句练习

由于数据库是我们it行业存储数据的核心，所以与it行业相关的职位，比如软开、前端、运维等都会考察数据库语句的使用，我们是必须要掌握sql语句的编写的。

sql语句分类

sql语句练习

最基本语句的回顾与练习：

创建库:create database databasename;

创建表:create table tablename(

……

);

查询: Select * from tablename;

删除记录: delete from tablename where ………..;

更改: update tablename set ……….. where …….;

清空表：两种方式

第一种：delete from tablename 清空表

第二种：truncate tablename

删除表: drop table tablename ;

删除库: drop database databasename;

聚合函数：

Max() 取最大值

Min() 取最小值

Sum() 求和

Count() 求行数

Avg() 求平均值

分组:

Group by

Group by …… having

排序

Order by 字段名 asc升序 desc降序

连接：

子查询:

解决中文乱码的问题

由于我们安装的mariadb数据库默认编码格式不是utf-8，所以我们如果在后续存储中文，就会出现乱码的问题，我们要进行解决。

查看目前数据库的编码格式:

show variables like ‘%char%’;

1、修改配置文件

在mariadb客户端的配置文件：/etc/my.cnf

里对应的模块下添加如下内容:

mysqld

character-set-server=utf8

client

default-character-set=utf8

mysql

default-character-set=utf8

在mariadb服务端的配置文件: /etc/my.cnf.d/server.cnf

里边的mysqld模块添加如下代码

init-connect='SET NAMES utf8'

character-set-server=utf8

然后重启数据库:

systemctl restart mariadb;

然后通过mysql命令进入数据库:

查看目前数据库的编码格式:

show variables like ‘%char%’;

可以看到数据库的默认编码格式改为utf8了

2、在创建数据库以及表时要指定编码格式

Create database yunwei default charset=utf8;

Create table ssss(

) default charset=utf8;

8、MySQL数据库用户安全策略介绍

安装完MySQL数据库之后，默认的管理员root密码为空，这很不安全。因此需要为root用户设置一个密码。如下图查询之后为空。

1、为root用户设置密码

进入mysql数据库：use mysql;

更改root用户密码： update user set password=password('song123') where user=root;

退出： exit;

注意：如上操作是不会生效的，再用“mysql -u root”登录的时候，依然是无密码状态，原因在于mysql服务为加速系统效率，会先将用户权限更新到内存中，除非使用实时生效命令会再次刷新内存权限数据。在mysql命令行中执行flush privileges,或重启服务，service mysqld restart centsos7 数据库的重启方式为systemctl restart mariadb

接下来无密码方式就不能登录，要采用以下方式登录

mysql -u root -p 回车后，会提示输入密码，输入自己设置的密码就好

2、忘记密码如何处理

进行免密登录的配置

首先停止MySQL服务，示例如下： systemctl stop mariadb

然后，/etc/my.cnf 中在mysqld的段中加上一句：skip-grant-tables

例如：

mysqld

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

skip-name-resolve

skip-grant-tables

保存并且退出vi。

现在，无须密码即可登录MySQL，然后修改新密码，示例如下：

这时，可以将root密码修改为新密码了，示例如下：

mysql> update mysql.user set password=PASSWORD("123456") where user="root" and host="localhost";

mysql> flush privileges; #<==刷新权限使得修改密码生效。

Query OK, 0 rows affected (0.01 sec)

然后重启服务 systemctl restart mariadb

3、MySQL创建用户及授权

命令如下:

grant all on school.* to test@localhost identified by 'song123'；

grant select, insert, update, delete on . to test@"%" identified by 'song123';

grant all on stumysql. * to test@ '192. 168. 111. 118' identified by 'song123'

以上三条 grant语句授权参数详解如下:

1、授权 localhost主机通过test用户和song123密码访问本地的stumysql库的所有权限;

2、授权所有主机通过test用户和song123s密码访间本地的 stumysql库的查询、插入、更新、删除权限;

3、授权192.168.111.118主机通过test用户和song123密码访问本地的 stumysql库的所有权限。

9、数据库备份与恢复

对于绝大多数企业来讲，失去数据就相当于失去商机、失去产品、失去客户，甚至会造成公司倒闭，那么，在所有的数据中，最核心的数据又是哪些呢？这恐怕要属数据库中的数据了，当然，并不是说其他数据不重要，只是这一部分更具代表性。既然数据库中的数据地位这么高，那么数据库备份与恢复的重要性就不言而喻了。

1、mysql数据库备份

基本备份输入命令:

mysqldump -u root -p stumysql1>/home/bak/2019-04-11/stumysql1.sql

Enter password: song123

此时,已成功将数据库stumysql1备份到/home/bak/2019-04-11目录下的test.sql中.

加-B参数备份的实践：

下面以备份stumysql1库时加-B参数，备份的文件中会比不加-B多创建库的语句，

在备份的时候最好加上-B,以下例进行讲解。

备份命令如下

mysqldump -B -u root -p stumysql1 >/home/bak/2019-04-11/stumysql.sql

Enter password: song123

指定压缩命令gzip压缩备份stumysql1数据库，命令如下：

mysqldump -B -u root -p stumysql1|gzip>/home/mysql_bak_B.sql.gz

#<==注意压缩命令前要加管道。

Linux增量备份

2、mysql数据库恢复

使用mysql命令

mysql -u root -p test < /home/bak/2019-04-11/stumysql1.sql

Enter password:song123

使用source命令恢复数据库的说明

进入mysql数据库控制台后，切换到想恢复数据的数据库。

mysql>use 数据库

接着，使用source命令进行恢复，后面接.sql文件，即上文使用mysqldump备份的文件或者人工编辑的SQL语句文件：

mysql>source /home/stumysql.sql

这个stumysql.sql文件是系统的相对路径，默认是登录MySQL前的系统路径，也可以使用完整的路径。

解压压缩包

gzip -d /home/mysql_bak_B.sql.gz

再用以上方式还原即可

10、MySQL数据库索引案例

MySQL索引可以用来快速地寻找某些具有特定值的记录,所有 MySQL.索引都以B-树形式保存。如果 MySQL没有索引,执行 select时会从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。如果表中数据有上亿条数据,查询一条数据花费的时间会非常长,索引类似于电子书的目录与页码的对应关系,可加快数据的查找。

如果在需搜索条件的列上创建了索引, MySQL无须扫描全表记录即可快速得到相应的记录行。如果该表有100000条记录,通过索引查找记录要比全表顺序扫描至少快100倍,这就是索引在企业环境中带来的执行速度上的提升。

1、MySQL数据库常见索引类型包括:普通索引( normal)、唯一索引( unique)、全文索引( full text)、主键索引( primary key)、组合索引等,以下为每个索引的应用场景及区别,

普通索引: normal,使用最广泛。

唯一索引: unique,不允许重复的索引,允许有空值

全文索引: full text,只能用于 MyISAM表, full text主要用于大量的内容检索

主键索引: primary key又称为特殊的唯一索引,不允许有空值。

组合索引:为提高 MySQL效率可建立组合索引。

2、MySQL数据库表创建各个索引命令,以t1表为案例,操作如下

主键索引: ALTER TABLE t1 ADD PRIMARY KEY(column)。

唯一索引: ALTER TABLE t1 ADD UNIQUE(column)

普通索引: ALTER TABLE t1 ADD INDEX index_name(column)。

全文索引: ALTER TABLE t1 ADD FULLTEXT(column)

组合索引: ALTER TABLE tl ADD INDEX index_name('columnl',' column2',' column3')

3、t1表的d字段创建主键索引,查看索引是否被创建,然后插入相同的id,提示报错,如图所示。

4、MySQL数据库表删除各个索引命令,以表为案例,操作如下:

DROP INDEX index_name ON t1;

ALTER TABLE tl DROP INDEX index_name;

ALTER TABLE tI DROP PRIMARY KEY;

5、MySQL数据库查看表索引,操作如下:

show index from t1;

show keys from t1;

11、MySQL数据库日志知识与企业应用实践

为了帮助管理员快速发现数据库的相关运行信息，MySQL为用户提供了几种日志种类，具体见表

默认情况下，以上所有的日志都处于非激活状态（Linux环境）。当激活日志时，所有的日志都默认配置在数据文件的目录下。管理员也可以对上述日志进行轮询切割，实现该功能常见的命令是mysqladmin flush-logs、mysqldump的“-F”或“--master-data”参数等，下面就分别介绍这几种日志知识。

(1)错误日志的介绍与配置

1.错误日志的介绍

MySQL的错误日志用于记录MySQL服务进程mysqld(mariadb)在启动/关闭或运行过程中遇到的错误信息。

2.错误日志的记录配置

MySQL的错误日志通常由mysqld或mysqld_safe程序产生，前文已经讲解过MySQL的启动原理，因此，可利用如下方法配置记录MySQL错误日志。

方法1：在my.cnf配置文件中调整，注意，是在mysqld_safe或mysqld模块的下面进行配置。命令如下：

mysqld

log-error = /data/mysql/errlog/error.err /data/mysql/errlog/error.err 都是自己手动创建的，记得修改所属的用户与所属的组为mysql ，或者修改操作权限

chown -R mysql:mysql /data/mysql

chmod -R 777 /data/mysql

方法2：在启动MySQL服务的命令里加入记录错误日志的参数。

示例如下：

log-error=/data/mysql/error/error.err

查看到的最终结果为：

show variables like 'log_error%';

修改文件夹的权限

3.错误日志轮询

管理员可以使用命令轮询错误日志，例如可以按天轮询，具体方法如下：

root@songls~# cd /data/mysql/error/ #<==切换到日志目录下。

root@songls mysql# mv error.err error_date %F.err #<==将错误日志按天

移动改名。

root@songls data# mysqladmin flush-logs #<==执行刷新日志命令。

root@songls data# ls -l error.err

-rw-rw----. 1 mysql mysql 0 Mar 19 19:34 error.err #<==新的错误日志诞生了。

4.数据库故障排查案例分析

新手安装数据库时，遇到数据库无法启动时的排查方法具体如下。

1）先清空错误日志文件，然后重新启动MySQL服务，再查看日志文件报什么错误，并根据错误日志进行处理。

2）如果无法解决，则删除数据文件，重新初始化数据库。

假设在排查故障时，得到的错误日志提示为：

180321 17:36:26 InnoDB: Completed initialization of buffer pool

180321 17:36:26 InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to

InnoDB: the directory.

InnoDB: File name ./ibdata1

根据提示可知，该错误是权限问题导致的问题，可对数据目录递归执行权限，然后再重启数据库。命令如下：

root@songls data# chown -R mysql:mysql /data/mysql/error

(2)慢查询日志

1.慢查询日志介绍

简单地理解，慢查询日志（slow query log）就是记录执行时间超出指定值（long_query_time）或其他指定条件（例如，没有使用到索引，结果集大于1000行）的SQL语句。

2.慢查询日志相关参数说明

慢查询的参数，对于数据库SQL的优化非常重要，是SQL优化的前提，因此，这里以表的形式进行说明，具体见表

3.慢查询日志重要参数配置

企业中常见的配置慢查询的参数为：

slow_query_log = ON #<== 慢查询开启开关

long_query_time = 2 #<==记录大于2秒的SQL语句。

log_queries_not_using_indexes #<==没有使用到索引的SQL语句。

slow-query-log-file = /data/mysql/slow/slow.log #<==记录SQL语句的文件。

min_examined_row_limit = 800 #<==记录结果集大于800行的SQL语句。

可将上述参数配置到my.cnf里，配置完毕重启MySQL服务，并进行检查：

mysql> show variables like 'slow_query%';

--------------------- -----------------------------

| Variable_name | Value |

--------------------- -----------------------------

| slow_query_log | ON | #<==开关已打开。

| slow_query_log_file | /data/mysql/slow.log | #<==文件路径已生效。

--------------------- -----------------------------

2 rows in set (0.00 sec)

mysql> show variables like '%long_query%';

----------------- ----------

| Variable_name | Value |

----------------- ----------

| long_query_time | 2.000000 | #<==记录大于2秒的查询已生效。

----------------- ----------

1 row in set (0.01 sec)

mysql> show variables like '%log_queries_not%';

------------------------------- -------

| Variable_name | Value |

------------------------------- -------

| log_queries_not_using_indexes | ON | #<==记录没有使用索引的查询已生效。

------------------------------- -------

1 row in set (0.00 sec)

mysql> show variables like '%min_examined_row_limit%';

------------------------ -------

| Variable_name | Value |

------------------------ -------

| min_examined_row_limit | 800 | #<==记录查询结果集大于800行的SQL已生效。

------------------------ -------

1 row in set (0.00 sec)

到此，就已经设定好记录慢查询SQL语句的条件了，那么，对于每天所产生的大量慢查询，又该如何处理和分析呢？

4.慢查询日志的刷新方法

在工作中，可以利用定时任务按天对慢查询日志进行切割，然后再分析。

示例切割脚本如下：

root@oldboy data# mkdir /server/scripts/ -p

root@oldboy data# cat /server/scripts/cut_slow_log.sh

export PATH=/application/mysql/bin:/sbin:/bin:/usr/sbin:/usr/bin

cd /data/mysql/slow

mv slow.log slow.log.$(date %F)

touch slow.log;chown -R mysql:mysql slow.log

将上述脚本放入定时任务，每天0点执行切割任务，配置结果如下：

root@oldboy data# tail -2 /var/spool/cron/root

#cut mysql slow log by oldboy at 20180324

00 00 * * * /bin/sh /server/scripts/cut_slow_log.sh >/dev/null 2>&1

5.使用工具分析慢查询日志

实际工作中，慢查询的日志可能非常多，给运维人员的优化工作带来了一定的困难，MySQL官方提供了慢查询的分析工具mysqldumpslow，有兴趣的读者可以参考官方手册。

下面为大家介绍一款很不错的第三方分析工具mysqlsla（需要单独安装该工具）。

（1）安装mysqlsla

请提前下载好mysqlsla-2.03.tar.gz到指定目录下，然后执行如下命令安装：

yum install perl-devel perl-DBI -y

rpm -qa perl-devel perl-DBI

tar -xf mysqlsla-2.03.tar.gz

cd mysqlsla-2.03

perl Makefile.PL

make

make install

（2）利用mysqlsla工具分析慢查询

mysqlsla命令的默认路径为：/usr/local/bin/mysqlsla。

简单语法如下：

mysqlsla -lt slow SlowLogFilePath > ResultFilePath

在实际工作中，通常使用脚本调用mysqlsla工具进行分析，然后每天早晨8点，把分析结果发给企业的核心人员（DBA、运维总监、CTO、研发总监、核心开发），最后由DBA配合核心开发共同优化这些棘手的SQL慢查询。

(3)二进制日志的介绍与配置

1.二进制日志的介绍

二进制日志的作用是记录数据库里的数据被修改的SQL语句，一般为DDL和DML语句，例如含有insert、update、delete、create、drop、alter等关键字的语句。

2.二进制日志的作用

二进制日志最重要的作用有2个，具体如下。

第一个是记录MySQL数据的增量数据，用来做增量数据库恢复，没有二进制日志功能，MySQL的备份将无法完整还原数据。

第二个是实现主从复制功能，具体见MySQL主从复制的相关内容。

3.二进制日志的配置

在/etc/my.cnf

log-bin=/data/mysql/bin/binlog.bin

root@songls data# grep log_bin /etc/my.cnf

log_bin #<==默认情况下记录日志前缀为“主机名-bin”。

mysql> show variables like 'log_bin';

--------------- -------

| Variable_name | Value |

--------------- -------

| log_bin | ON | #<==记录binlog开关。

--------------- -------

1 row in set (0.00 sec)

mysql> show variables like '%log_bin';

--------------- -------

| Variable_name | Value |

--------------- -------

| log_bin | ON | #<==记录binlog开关。

| sql_log_bin | ON | #<==临时不记录binlog开关。

--------------- -------

2 rows in set (0.00 sec)

有个参数可以实现在开启binlog功能的前提下，临时不记录binlog，示例如下：

mysql> set session sql_log_bin = OFF; #<==临时停止记录binlog，注意是session

级别，不影响其他会话。

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%log_bin';

--------------- -------

| Variable_name | Value |

--------------- -------

| log_bin | ON |

| sql_log_bin | OFF | #<==已关闭。

--------------- -------

2 rows in set (0.00 sec)

mysql> create database oldgirl; #<==建库测试。

Query OK, 1 row affected (0.00 sec)

mysql> show binary logs; #<==查看binlog文件列表及位置点。

------------------- -----------

| Log_name | File_size |

------------------- -----------

| oldboy-bin.000001 | 143 |

| oldboy-bin.000002 | 168 |

| oldboy-bin.000003 | 168 |

| oldboy-bin.000004 | 9299 |

| oldboy-bin.000005 | 211 | #<==最新的binlog文件及位置点，也可以通过

“show master status;”来确定。

------------------- -----------

5 rows in set (0.00 sec)

mysql> system mysqlbinlog oldboy-bin.000005|grep "oldgirl"

#<==过滤binlog文件，没有记录binlog。

mysql> set session sql_log_bin = On; #<==开启开关。

Query OK, 0 rows affected (0.00 sec)

mysql> drop database oldgirl; #<==删除数据库。

Query OK, 0 rows affected (0.00 sec)

mysql> system mysqlbinlog oldboy-bin.000005|grep "oldgirl"

#<==继续过滤，发现记录了binlog。

drop database oldgirl

到这里，读者应该知道sql_log_bin的功能了吧，这个功能通常用于在用户使用mysql恢复数据时不希望恢复的数据SQL记录到binlog里的情况。当然，还有其他的应用场景。

4.二进制日志文件的刷新条件

1）数据库重启会自动刷新binlog为新文件。

2）执行“mysqldump -F”或“mysqladmin flush-logs”会将binlog刷新为新文件。

3）binlog文件达到1GB左右时，会自动刷新binlog为新文件。

4）人为配置切割及调整。

binlog最大值控制参数及默认大小查看方法如下：

mysql> show variables like 'max_binlog_size';

----------------- ------------

| Variable_name | Value |

----------------- ------------

| max_binlog_size | 1073741824 |

----------------- ------------

1 row in set (0.00 sec)

5.二进制日志索引文件

除了很多按序列生成的binlog文件列表之外，还有一个索引文件，例如下文里的oldboy-bin.index：

root@oldboy data# pwd

/application/mysql/data

root@oldboy data# ls -l oldboy-bin.*

-rw-rw----. 1 mysql mysql 143 Mar 3 05:50 oldboy-bin.000001

-rw-rw----. 1 mysql mysql 168 Mar 3 05:57 oldboy-bin.000002

-rw-rw----. 1 mysql mysql 168 Mar 3 05:57 oldboy-bin.000003

-rw-rw----. 1 mysql mysql 9299 Mar 19 19:34 oldboy-bin.000004

-rw-rw----. 1 mysql mysql 211 Mar 19 20:15 oldboy-bin.000005

-rw-rw----. 1 mysql mysql 100 Mar 19 19:34 oldboy-bin.index

索引文件的文件名和binlog文件一样，只是扩展名为index，查看索引文件内容的命令如下：

root@oldboy data# cat oldboy-bin.index

./oldboy-bin.000001

./oldboy-bin.000002

./oldboy-bin.000003

./oldboy-bin.000004

./oldboy-bin.000005

binlog索引文件的控制参数为：

mysql> show variables like 'log_bin_index';

--------------- -------------------------------------------------

| Variable_name | Value |

--------------- -------------------------------------------------

log_bin_index | /application/mysql-5.6.40/data/oldboy-bin.index |

--------------- -------------------------------------------------

1 row in set (0.00 sec)

6.删除二进制日志的方法

binlog日志很重要，不能随意清除，有些读者看到所维护的服务器空间满了，竟然会直接删除binlog物理文件，这样的操作是错误的，应避免。那么如何正确删除binlog文件呢？

首先，要确定什么时候可以删除binlog。

理论上每天的数据库全备时刻以前的binlog都是无用的，但是工作中我们会根据需要保留3~7天的本地binlog文件。

下面来看看具体的删除方式。

（1）设置参数自动删除binlog

设置参数自动删除binlog是每个管理员都应该做的，参数设置示例如下。

假设参数为：

expire_logs_days = 7 #<==删除7天前的日志

该参数默认是没有配置的，生产中可以同时实现在线更改以及永久更改配置文件：

mysql> show variables like 'expire_logs_days';

------------------ -------

| Variable_name | Value |

------------------ -------

| expire_logs_days | 0 |

------------------ -------

1 row in set (0.00 sec)

mysql> set global expire_logs_days = 7;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'expire_logs_days';

------------------ -------

| Variable_name | Value |

------------------ -------

| expire_logs_days | 7 |

------------------ -------

1 row in set (0.00 sec)

root@oldboy data# grep expir /etc/my.cnf

expire_logs_days = 7

（2）从最开始一直删除到指定的文件位置（不含指定文件）

这种方法一般用于处理临时的需求，操作如下：

root@oldboy data# cp oldboy-bin.* /tmp

登录数据库时执行如下命令：

mysql> show binary logs;

------------------- -----------

| Log_name | File_size |

------------------- -----------

| oldboy-bin.000001 | 143 |

| oldboy-bin.000002 | 168 |

| oldboy-bin.000003 | 168 |

| oldboy-bin.000004 | 9299 |

| oldboy-bin.000005 | 211 |

------------------- -----------

4 rows in set (0.00 sec)

mysql> purge binary logs to 'oldboy-bin.000002';

Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;

------------------- -----------

| Log_name | File_size |

------------------- -----------

| oldboy-bin.000002 | 168 | #<==序列000002以前的就没了。

| oldboy-bin.000003 | 168 |

| oldboy-bin.000004 | 9299 |

| oldboy-bin.000005 | 211 |

------------------- -----------

4 rows in set (0.00 sec)

（3）按照时间删除binlog日志

这种方法也是用于处理临时的需求，操作如下：

root@oldboy data# ls -l --time-style=long-iso oldboy-bin*

-rw-rw----. 1 mysql mysql 168 2018-03-03 05:57 oldboy-bin.000002

-rw-rw----. 1 mysql mysql 168 2018-03-03 05:57 oldboy-bin.000003

-rw-rw----. 1 mysql mysql 9299 2018-03-19 19:34 oldboy-bin.000004

----------------------- ----------------------

2 rows in set (0.00 sec)

（2）max_binlog_size

该参数用于设置binlog日志的最大大小，默认为1GB，但是该值并不能严格控制binlog的大小。若binlog大小接近1GB，而此时又在执行一个较大的事务，那么为了保证事务的完整性，数据库不会做日志刷新动作，而是直到该事务的日志全部记录进入当前binlog日志后才会进行刷新。该参数的默认值查询结果为：

mysql> show variables like '%max_binlog_size%';

----------------- ------------

| Variable_name | Value |

----------------- ------------

| max_binlog_size | 1073741824 |

----------------- ------------

1 row in set (0.00 sec)

（3）sync_binlog

这个参数的作用是控制binlog什么时候同步到磁盘。对数据库来说，这是很重要的参数，它不仅会影响数据库的性能，还会影响数据库数据的完整性。

对于“sync_binlog”参数的说明具体如下。

·“sync_binlog=0”表示在事务提交之后，数据库不会将binlog_cache中的数据刷新到磁盘，而是让文件系统自行决定什么时候来做刷新或者在缓存满了之后才刷新到磁盘。

·“sync_binlog=n”表示每进行n次事务提交之后，数据库都会进行一次将缓存数据强制刷新到磁盘的操作。

该参数默认的设置是0，示例如下：

mysql> show variables like '%sync_binlog%';

--------------- -------

| Variable_name | Value |

--------------- -------

| sync_binlog | 0 |

--------------- -------

1 row in set (0.00 sec)

设置为0时数据库的性能是最好的，但数据风险也是最大的，对于数据安全性要求较高的数据库，应该调整该参数将其改为1，值得注意的是，即使参数设置为1，仍然有binlog记录的内容与数据库的实际内容不一致的风险。

7.记录二进制日志的三种模式

MySQL使用不同的模式记录二进制日志信息，常见的有三种模式。

（1）语句模式

语句（statement-based）模式是MySQL5.6版本默认的模式，简单地说，就是每一条被修改的数据的SQL语句都会记录到master的binlog中。在复制slave库的时候，SQL进程会解析成与原来master端执行过的相同的SQL来再次执行。

该模式的优点是不需要记录细到每一行数据的更改变化，因此，可减少binlog日志量，实际上是减少了很多，节约了磁盘I/O，提高了系统性能。

但该模式同样有一些缺点，由于语句模式记录的是执行的SQL语句，所以，对于某些具有特殊功能的SQL语句来说，就可能会导致无法在从库上正确执行，从而导致主从库数据不一致的问题。

例如，当特殊的函数被执行时，当触发器、存储过程等特殊功能被执行时，而row level模式是基于每一行来记录变化的，所以不会出现类似的问题（更多详情请参考混合模式）。

（2）行级模式

简单地说，行级（row-based）模式就是将数据被修改的每一行的情况记录为一条语句。

优点：在行级模式下，binlog中可以不记录执行的SQL语句的上下文相关信息，仅仅记录哪一条记录被修改了，修改成什么样了即可，所以row level的日志内容会非常清楚地记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题。

缺点：行级模式下，所有的执行语句都将根据修改的行来记录，而这就可能会产生大量的日志内容，例如一条语句修改了100万行，语句模式就用一条语句即可搞定，而行级模式执行之后，日志中记录的就是100万行的修改记录，binlog日志的量可能会大得惊人。

（3）混合模式

混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于以下情况。

·当函数中包含UUID()时。

·当表中有自增列（AUTO_INCREMENT）被更新时。

·当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。

·当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。

8.企业中如何选择二进制日志模式

在互联网公司中，使用MySQL的特殊功能比较少（存储过程、触发器、函数），此时可以选择默认的语句模式。

如果公司较多用到MySQL的特殊功能，如存储过程、触发器、函数等，并且需要做主从复制请首选行级模式，次选mixed模式。

9.二进制日志的模式配置调整

临时调整命令如下：

mysql> SET GLOBAL binlog_format = 'STATEMENT';

mysql> SET GLOBAL binlog_format = 'ROW';

@1=5

@2='test'

UPDATE `oldboy`.`student`

WHERE

@1=6

@2='oldgirl1'

SET

@1=6

@2='test'

at 500

12、MySQL数据库优化

MySQL数据库优化是一项非常重要的工作,而且是一项长期的工作, MySQL优化三分靠配置文件及硬件资源的优化,七分靠SQL语句的优化。

MySQL数据库具体优化包括:配置文件的优化、SQL语句的优化、表结构的优化、索引的优化,而配置的优化包括:系统内核、硬件资源、内存、CPU、 MySQL本身配置文件的优化。

硬件上的优化有两种方式:一种是增加内存和提高磁盘读写速度,进而提高 MySQL数据库的查询、更新的速度;另一种提高 MySQL性能的方式是使用多块磁盘来存数可以从多块磁盘上并行读取数据,进而提高读取数据的速度MySQL参数的优化,内存中会为 MySQL保留部分的缓冲区,这些缓冲区可以提高MySQL.的速度,缓冲区的大小可以在 MySQL的配置文件中进行设置

以下为企业级 MySQL百万量级真实环境配置文件my.cnf的内容,用户可以根据实情况修改,代码如下：

client

port=3306

socket =/tmp/mysql. sock

mysqld

mysql

server id= 10

port=3306

socket=/tmp/mysql. sock

datadir =/data/mysql/

old_passwords=1

lower_case_table_names=1

character-set- server=utf8

default- storage-engine=MYISAM

log-bin=bin. log

log-error=error.log

pid-file= mysql. pid

long_ query_time=2

slow_query_log=ON

slow_query_log_file=slow.log

binlog_cache_size= 4MB

binlog_format=mixed

max_binlog_cache_size=16MB

max_binlog_size= 1GB

expire_logs_days= 30

ft_min_word_len =4

back_log=512

max_allowed_packet= 64MB

max_connections=4096

max_connect_errors= 100

join_buffer_size= 2MB

read_buffer_size= 2MB

read_rnd_buffer_size=2MB

sort_buffer_size= 2MB

query_cache_size= 64MB

table_open_cache= 10000

thread_cache_size=256

max_heap_table_size= 64MB

tmp_table_size=64MB

thread_stack=192KB

thread_concurrency=24

local-infile=0

skip-show-database

skip-name- resolve

skip-external-locking

connect_timeout=600

interactive_timeout=600

wait_timeout=600

#***MyISAM

key_buffer_size= 512MB

bulk_insert_buffer_size=64MB

myisamsort buffer_size=64MB

myisam_max_sort_file_size=1GB

myisam_repair_threads=1

concurrent_insert= 2

myisam_recover

*** INNODB

innodb_buffer_pool_size= 64GB

innodb_additional_mem_pool_size= 32MB

innodb_data_file_path=ibdata1:1G; ibdata2: 1G:autoextend

innodb_read_io_threads=8

innodb_write_io_threads =8

innodb_file_per_table= 1

innodb_flush_log_at_trx_commit =2

innodb_lock_wait_timeout=120

innodb_log_buffer_size= 8MB

innodb_log_file_size=256MB

innodb_log_files_in_group =3

innodbmax_dirty_pages pct= 90

innodb_thread_concurrency =16

innodb_open_files =10000

*** innodb_force_recovery =4

*** Replication Slave

read-only

#skip- slave-start

relay-log= relay. log

log-slave-updates

13、MySQL数据库集群实战(主从复制、读写分离)

随着访问量的不断增加,单台 MySQL数据库服务器压力不断地增加,需要对 MySQL进行优化和架构改造,如果 MyQSL优化不能明显改善压力,可以使用高可用、主从复制、读MySQL.主从复制集群在中小企业、大型企业中被广泛应用, MYSQL主从复制的的写分离来、拆分库、拆分表等方法来进行优化。

是实现数据库冗余备份,将 master数据库数据定时同步至 slave库中,一旦 master数

宕机,可以将Web应用直接迁移slave上

MySQL主从复制集群至少需要2台数据库服务器,其中一台为 master库,另外一台为slave库, MySQL主从数据同步是一个异步复制的过程,要实现复制首先需要在 master上开启 bin-log日志功能, bin-log日志用于记录在 master库中执行的增、删、修改、更新操作的SQL语句,整个过程需要开启3个线程,分别是 master开启1/0线程, slave开启I/O线程和SQL线程,具体主从同步原理详解如下

1、slave上执行 slave start, slave I/O线程会通过在 master创建的授权用户连接上至master,

并请求 master从指定的文件和位置之后发送 bin-log日志内容

2、master接收到来自 slave I/O线程的请求后, master I/O线程根据slave发送的指定bin-log日志 position点之后的内容,然后返回给slave的1/O线程

3、返回的信息中除了 bin-log日志内容外,还有 master最新的 bin-log文件名以及在bin-log中的下一个指定更新 position点;

4、slave I/O线程接收到信息后,将接收到的日志内容依次添加到slave端的 relay-log

文件的最末端,并将读取到的 master端的 bin-log的文件名和 posion点记录master.info文件中,以便在下一次读取的时候能告知 master从相应的bin-log文件名及最后一个 position点开始发起请求

5、slave SQL线程检测到 relay-log中内容有更新,会立刻解析relay-log日志中的内容,

将解析后的SQL语句在slave里执行,执行成功后slave库与master数保持一致

14、mysql主从复制

MysQL主从复制环境构建至少需2台服务器,可以配置1主多从,多主多从,以1主1

从为例,MySQ1.主从复制架构实战步骤如下。

(1)系统环境准备

master:192.168.56.165

slave:192.168.56.145

(2) master安装及配置

master端使用源码安装MySQl.5.5版本软件后,在/etc/my.cnf配置文件 mysqld段中加入如下代码,然后重启 MySQL服务即可,如果在安装时 cp my-large.cnf /etc/ my.cnf,则无须添加如下代码:

server-id=1

log-bin=mysql-bin

master端/etc/my.cnf完整配置代码如下:

client

port=3306

socket=/tmp/mysql. sock

mysqld

port=3306

socket=/tmp/mysql.sock

skip-external-locking

key_buffer_size= 256MB

max_allowed_packet= 1MB

table_open_cache=256MB

sort_buffer_size=1MB

read_buffer_size=1MB

read_rnd_buffer_size=4MB

myisam_sort_buffer_size=64MB

thread_cache_size=8

query_cache_size=16MB

thread_concurrency=8

log-bin=mysql-bin

binlog_format=mixed

server- id= 1

mysqldump

quick

max_allowed_packet=16MB

mysql

no- auto- rehash

myisamchk

key_buffer_size= 128MB

sort_buffer_size= 128MB

read_buffer= 2MB

write_buffer= 2MB

mysqlhotcopy

interactive-timeout

在 master数据库服务器命令行中创建 tongbu用户及密码并设置权限,执行如下查看 bin-log文件及 position点，如图所示。

grant replication slave on . to 'tongbu'@'%’ identified by 'song123';

show master status;

(3) slave安装及配置

slave:端使用源码安装MySQL 5.5版本软件后,在/etc/my.cnf配置文件 mysqld段中加入如下代码,然后重启 MySQL服务即可,如果在安装时 cp my-large.cnf /etc/my.cnf,则需修改 server-id, master与 slave端 server-id不能一样,slave端无须开启 bin-log功能,代码如下:

server-id= 2

slave端/etc/my.cnf完整配置代码如下:

client

port=3306

socket=/tmp/mysql.sock

mysqld

port=3306

skip-external-locking

key_buffer_size=256MB

max_allowed_packet=1MB

table_open_cache=256

sort_buffer_size=1MB

read_buffer_size=1MB

read_rnd_buffer_size=4MB

myisam_sort_buffer_size=64MB

thread_cache_size=8

query_cache_size= 16MB

thread_concurrency=8

server-id=2

mysqldump

quick

max_allowed_packet=16MB

no-auto-rehash

myisamchk

key_buffer_size=128MB

sort_buffer_size= 128MB

read_buffer=2MB

write_buffer=2MB

mysqlhotcopy

interactive- timeout

为slave指定 master IP、用户名、密码、 bin-log文件名( mysql-bin.00028)及position(257),

代码如下:

change master to

master_host=’192.168.56.165',master_user='tongbu',master_password=’song123’, master_log_file='mariadb-bin.000003’, master_log_pos=391;

如图：

在 slave中启动 slave start,并执行 show slave statusG查看 MySQL.主从状态,代码如下:

slave start;

show slave statusG

如图

查看 slave端I/O线程、SQL.线程状态均为Yes,代表 slave已正常连接 master实现同

步,代码如下:

slaveIO Running:Yes

slave_SQL_Running:Yes

执行 Show slave statusG,常见参数含义解析如下:

Slave_IO_State: I/O线程连接 master状态

Master_User:用于连接 master的用户

Master_Port_a: master端监听端口

Connect_Retry:主从连接失败,重试时间间隔。

Master_Log_File:I/O线程读取的 master二进制日志文件的名称。

Read_Master_Log_Pos:IO线程已读取的 master二进制日志文件的位置。

Relay_Log_File:SQL线程读取和执行的中继日志文件的名称

Relay_Log_Pos: SQL线程已读取和执行的中继日志文件的位置。

Relay_Master_Log_File:SQL线程执行的 master二进制日志文件的名称

Slave_IO_Running:I/O线程是否被启动并成功地连接到主服务器上。

Slave_SQL_Running:SQL线程是否被启动。

Replicate_Do_DB:指定的同步的数据库列表

Skip_Counter:SQL_SLAVE_SKIP_COUNTER设置的值。

Seconds_Behind_Master:slave端SQL线程和1/O线程之间的时间差距,单位秒,常被用于主从延迟检查方法之一。

(4)进行测试：

在master端创建tongbu数据库和stu表,命令如下,详情如图所示

mysql> create database tongbu character set utf8;

Query OK, 1 row affected (0.00 sec)

mysql> use tongbu;

Database changed

mysql> create table stu(

-> sid int primary key auto_increment,

-> snam varchar(20),

-> sage int

-> )character set utf8;

Query OK, 0 rows affected (0.00 sec)

然后在slave库上进行查看，发现进行了随时进行了备份

MySQL.主从架构至此配置成功。

在master服务器stu表中插入两条数据，命令如下，在slave查看是否同步，master上执行详情如图所示。

Slave端执行查询命令,如图所示,表示master端插人的SQL数据已经同步到slave端

读写分离

大多数互联网业务，往往读多写少，这时候，数据库的读会首先成为数据库的瓶颈，这时，如果我们希望能够线性的提升数据库的读性能，消除读写锁冲突从而提升数据库的写性能，那么就可以使用“分组架构”（读写分离架构）。

配置好了 Mysql 的主从复制结构后，我们希望实现读写分离，把读操作分散到从服务器中，并且对多个从服务器能实现负载均衡。读写分离和负载均衡是 Mysql 集群的基础需求，MaxScale 就可以帮着我们方便的实现这些功能。

MaxScale 的基础构成

MaxScale 是Mysql 的兄弟公司 MariaDB 开发的，现在已经发展得非常成熟。MaxScale 是插件式结构，允许用户开发适合自己的插件。

MaxScale 目前提供的插件功能分为5类

1、认证插件

提供了登录认证功能，MaxScale会读取并缓存数据库中 user 表中的信息，当有连接进来时，先从缓存信息中进行验证，如果没有此用户，会从后端数据库中更新信息，再次进行验证。

2、协议插件

包括客户端连接协议，和连接数据库的协议。

3、路由插件

决定如何把客户端的请求转发给后端数据库服务器，读写分离和负载均衡的功能就是由这个模块实现的。

4、监控插件

对各个数据库服务器进行监控，例如发现某个数据库服务器响应很慢，那么就不向其转发请求了。

5、日志和过滤插件

提供简单的数据库防火墙功能，可以对SQL进行过滤和容错。

配置方式

准备3台服务器，安装 Mysql，配置一主二从的复制结构。主从复制的配置过程参考上一节内容。

安装 MaxScale

最好在另一台服务器上安装，如果资源不足，可以和某个 Mysql 放在一起。

MaxScale 的下载地址，最新版本2.3.4，实例中使用的是1.4.5：

https://downloads.mariadb.com/files/MaxScale

根据自己的服务器选择合适的安装包，以 centos 7 为例安装步骤如下：

yum install libaio.x86_64 libaio-devel.x86_64 novacom-server.x86_64 libedit -y

wget https://downloads.mariadb.com/MaxScale/1.4.5/centos/7/x86_64/maxscale-1.4.5-1.centos.7.x86_64.rpm

rpm -ivh maxscale-1.4.5-1.centos.7.x86_64.rpm

配置MaxScale

在开始配置之前，需要在 master 中为 MaxScale 创建两个用户，用于监控模块和路由模块：

1、创建监控用户

create user 'maxmon'@'%' identified by '123456';

grant replication slave,replication client on . to 'maxmon'@'%';

2、创建路由用户

create user 'maxrou'@'%' identified by '123456';

grant select on mysql.* to 'maxrou'@'%';

flush privileges;

3、修改maxscale配置文件

用户创建完成后，开始配置

vi /etc/maxscale.cnf

找到 server1 部分，修改其中的 address 和 port，指向 master 的 IP 和端口复制2次 server1 的整块儿内容，改为 server2 与 server3，同样修改其中的 address 和 port，分别指向 slave1 和 slave2

找到 MySQL Monitor 部分，修改 servers 为 server1,server2,server3，修改 user 和 passwd 为之前创建的监控用户的信息（maxmon,123456）

找到 Read-Write Service 部分，修改 servers 为 server1,server2,server3，修改 user 和 passwd 为之前创建的路由用户的信息（maxrou,123456）

由于我们使用了 Read-Write Service，需要删除另一个服务 Read-Only Service，删除其整块儿内容即可，底部还有一个Read-Only Client也需要删除。

配置完成，保存并退出编辑器。

启动MaxScale

执行启动命令

maxscale --config=/etc/maxscale.cnf

查看 MaxScale 的响应端口是否已经就绪

netstat -ntelp

4006 是Read-Write Listener使用的端口，用于连接MaxScale

6603 是MaxAdmin Listener使用的端口，用于MaxScale 管理器

maxadmin --user=admin --password=mariadb

MaxScale> list servers

可以看到，MaxScale 已经连接到了 master 和 slave

测试

1、先在 master 上创建一个测试用户

create user 'rtest'@'%' identified by '111111';

grant ALL PRIVILEGES on . to 'rtest'@'%';

2、使用 Mysql 客户端到连接 MaxScale

mysql -urtest -p'111111' -h'192.168.33.11' -P4006

3、执行查看数据库服务器名的操作来知道当前实际所在的数据库

Select @@hostname；

start transaction;

Select @@hostname；

roolback;

Select @@hostname；

开启事务后，就自动路由到了 master，普通的查询操作，是在 slave上。

总结

通过MaxScale可以很轻松的实现MySQL数据库的读写分离和负载均衡。

配置MySQL多实例

什么是Mysql多实例

简单地说，MySQL多实例就是在一台服务器上同时开启多个不同的服务端口（如：3306,3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。

这些MySQL多实例共用一套MySQL安装程序，使用不同的my.cnf（也可以相同）配置文件、启动程序（也可以相同）和数据文件。在提供服务时，多实例MySQL在逻辑上看来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。

打个比方吧，MySQL多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（cpu,men,disk）、软件资源（CentOS操作系统）可以看作房子的卫生间、厨房、客厅，是房子的共用资源。若你是北漂的小伙伴，与朋友一起租房子，相信更好理解，大家蜗居在一起，休息在自己的卧室，出来活动肯定是要共用上述公共资源。这样就可以很好的理解MySQL多实例了。

其实很多网络服务都是可以配置多实例的，例如nginx、Apache、haproxy、redis等都可以配置多实例。这在门户网站使用都很广泛。

MySQL多实例的作用于问题

有效利用服务器资源

当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务，且可以实现资源的逻辑隔离。

节约服务器资源

当公司资金紧张，但是数据库又需要各自尽量独立地提供服务，而且，需要主从复制等技术，多实例就再好不过了。

MySQL多实例有它的好处，但也有其弊端，比如，会存在资源互相抢占的问题。

当某个数据库实例并发很高或者有SQL慢查询时，整个实例会消耗大量的系统CPU、磁盘I/O等资源，导致服务器上的其他数据库实例提供服务的质量一起下降。这就相当于大家住在一个房子的不同卧室一样，早晨起来上班，都要刷牙、洗脸等，这样卫生间就会长期占有，其他人就要等待一样。不同实例获取的资源是相对独立的，无法像虚拟化一样完全隔离。

在主机192.168.4.57上：

配置第1个MySQL实例

实例名称mysqld1、端口3307

数据库目录/dir2、pid文件mysqld1.pid

错误日志mysqld1.err、socket文件mysqld1.socket

配置第2个MySQL实例

实例名称mysqld2、端口3308

数据库目录/dir1、pid文件mysqld2.pid

错误日志mysqld2.err、socket文件mysqld2.socket

步骤一：配置多实例（192.168.4.57上操作）

什么是多实例：

在一台物理主机上运行多个数据库服务，可以节约运维成本，提高硬件利用率

1）解压软件、修改目录名、设置PATH路径

1.]# yum –y install libaio

2.]# useradd mysql

3.]# tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

4.]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql

5.]# PATH=/usr/local/mysql/bin:$PATH

6.]# vim /etc/bashrc

7.export PATH=/usr/local/mysql/bin:$PATH

8.:wq

2）编辑主配置文件/etc/my.cnf

每个实例要有独立的数据库目录、监听端口号、实例名称和独立的sock文件

1.]# vim /etc/my.cnf

2.mysqld_multi //启用多实例

3.mysqld = /usr/local/mysql/bin/mysqld_safe //指定进程文件路径

4.mysqladmin = /usr/local/mysql/bin/mysqladmin //指定管理命令路径

5.user = root //指定进程用户

7.mysqld1 //实例进程名称

8.port=3307 //端口号

9.datadir=/dir1 //数据库目录，要手动创建

10.socket=/dir1/mysqld1.sock //指定sock文件的路径和名称

11.pid-file=/dir1/mysqld1.pid //进程pid号文件位置

12.log-error=/dir1/mysqld1.err //错误日志位置

13.

14.mysqld2

15.port=3308

16.datadir=/dir2

17.socket=/dir2/mysqld2.sock

18.pid-file=/dir2/mysqld2.pid

19.log-error=/dir2/mysqld2.err

20.:wq

3）创建数据库目录

1.]# mkdir /dir2

2.]# mkdir /dir1

4）启动多实例

首次启动服务会做数据初始化并初始和提示数据库管理员本机登录密码

1.root@host57 ~# mysqld_multi start 1 //启动实例1

3.Installing new database in /dir1

5.2019-06-13T10:46:29.307866Z 0 Warning TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

6.2019-06-13T10:46:30.997233Z 0 Warning InnoDB: New log files created, LSN=45790

7.2019-06-13T10:46:31.436904Z 0 Warning InnoDB: Creating foreign key constraint system tables.

8.2019-06-13T10:46:31.582129Z 0 Warning No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 816bf015-8dc8-11e9-b492-525400cffedc.

9.2019-06-13T10:46:31.605276Z 0 Warning Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

10.2019-06-13T10:46:31.606321Z 1 Note A temporary password is generated for root@localhost: ly#LryiFE5fT 管理员本机登录密码

11.

12.]# ls /dir1 //查看数据库目录文件列表

13.auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql3307.log mysql3307.pid mysql3307.sock mysql3307.sock.lock performance_schema sys

14.

15.]# mysqld_multi start 2 //启动实例2

16.

17.

18.Installing new database in /dir1

19.

20.2019-06-13T10:56:55.580796Z 0 Warning TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

21.2019-06-13T10:56:57.199217Z 0 Warning InnoDB: New log files created, LSN=45790

22.2019-06-13T10:56:57.571839Z 0 Warning InnoDB: Creating foreign key constraint system tables.

23.2019-06-13T10:56:57.708168Z 0 Warning No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f69f30fa-8dc9-11e9-8a17-525400cffedc.

24.2019-06-13T10:56:57.724096Z 0 Warning Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

25.2019-06-13T10:56:57.724677Z 1 Note A temporary password is generated for root@localhost: qedTjrZs*8ma 管理员本机登录密码

26.

27.]# ls /dir1 //查看数据库目录文件列表

28.auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql3308.log mysql3308.pid mysql3308.sock mysql3308.sock.lock performance_schema sys

5）查看端口

1.]# netstat -utnlp | grep :3307

2.tcp6 0 0 :::3307 :::* LISTEN 1151/mysqld

4.]# netstat -utnlp | grep :3308

5.tcp6 0 0 :::3308 :::* LISTEN 1339/mysqld

7.]# netstat -utnlp | grep mysqld

8.tcp6 0 0 :::3307 :::* LISTEN 1151/mysqld

9.tcp6 0 0 :::3308 :::* LISTEN 1339/mysqld

10.

11.# ps -C mysqld

PID TTY TIME CMD11.Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 12. 13.Oracle is a registered trademark of Oracle Corporation and/or its 14.affiliates. Other names may be trademarks of their respective 15.owners. 16. 17.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 18. 19.mysql> show databases; 20. -------------------- 21.| Database | 22. -------------------- 23.| information_schema | 24.| mysql | 25.| performance_schema | 26.| sys | 27. -------------------- 28.4 rows in set (0.00 sec) 29. 30.mysql> create database db1; //创建新库db1 31.Query OK, 1 row affected (0.00 sec) 32. 33.mysql> show databases; //查看已有的库 34. -------------------- 35.| Database | 36. -------------------- 37.| information_schema | 38.| db1 | //db1库 39.| mysql | 40.| performance_schema | 41.| sys | 42. -------------------- 43.5 rows in set (0.00 sec) 44. 45.mysql> exit //断开连接 46.Bye 47.root@host56 ~# ls /dir1 //查看数据库目录文件列表有db1库的文件夹 48.auto.cnf ibdata1 ibtmp1 mysqld1.pid performance_schema 49.db1 ib_logfile0 mysql mysqld1.socket sys 50.ib_buffer_pool ib_logfile1 mysqld1.err mysqld1.socket.lock 51.root@host56 ~# 使用初始化密码登录实例2 1.root@host57 ~# mysql -uroot -p'qedTjrZs8ma' -S /dir2/mysqld2.sock 2. mysql> alter user root@"localhost" identified by "654321"; //修改密码 3.mysql> exit 4.Bye 5.root@host57 ~# mysql -uroot –p654321 -S /dir2/mysqld2.sock //新密码登录 6.mysql: Warning Using a password on the command line interface can be insecure. 7.Welcome to the MySQL monitor. Commands end with ; or g. 8.Your MySQL connection id is 4 9.Server version: 5.7.20 MySQL Community Server (GPL) 10. 11.Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 12. 13.Oracle is a registered trademark of Oracle Corporation and/or its 14.affiliates. Other names may be trademarks of their respective 15.owners. 16. 17.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 18. 19.mysql> show databases; 20. -------------------- 21.| Database | 22. -------------------- 23.| information_schema | 24.| mysql | 25.| performance_schema | 26.| sys | 27. -------------------- 28.4 rows in set (0.00 sec) 29. 30.mysql> 31.mysql> create database db2; 32.Query OK, 1 row affected (0.00 sec) 33. 34.mysql> show databases; 35. -------------------- 36.| Database | 37. -------------------- 38.| information_schema | 39.| db2 | 40.| mysql | 41.| performance_schema | 42.| sys | 43. -------------------- 44.5 rows in set (0.00 sec) 45. 46.mysql> exit 47.Bye 48.root@host56 ~# ls /dir2 49.auto.cnf ib_logfile0 mysqld2.err performance_schema 50.db2 ib_logfile1 mysqld2.pid sys 51.ib_buffer_pool ibtmp1 mysqld2.socket 52.ibdata1 mysql mysqld2.socket.lock 53.root@host56 ~# 7）停止多实例服务 mysqld_multi --user=root --password=密码 stop 实例编号 1.]# netstat -utnlp | grep mysqld 2.tcp6 0 0 :::3307 ::: LISTEN 1250/mysql 3.tcp6 0 0 :::3308 ::: LISTEN 1451/mysql 4. 5.]# mysqld_multi --user=root --password=123456 stop 2 6. 7. root@host56 ~# netstat -utnlp | grep mysqld 8.tcp6 0 0 :::3307 ::: LISTEN 1250/mysql 9. 10.]# mysql -uroot -p123456 -S /dir2/mysqld2.sock //拒绝连接 11.mysql: Warning Using a password on the command line interface can be insecure. 12.ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/dir2/mysqld2.sock' (2)
1151 pts/1 00:00:00 mysqld
1339 pts/1 00:00:00 mysqld 15.root@host57 ~# 6）访问多实例使用初始化密码登录实例1 1.root@host57 ~# mysql -uroot -p'ly#LryiFE5fT' -S /dir1/mysqld1.sock 2. mysql> alter user root@"localhost" identified by "123456"; //修改密码 3.mysql> exit 4.Bye 5.root@host57 ~# mysql -uroot -p123456 -S /dir1/mysqld1.sock //新密码登录 6.mysql: Warning Using a password on the command line interface can be insecure. 7.Welcome to the MySQL monitor. Commands end with ; or g. 8.Your MySQL connection id is 4 9.Server version: 5.7.20 MySQL Community Server (GPL)

数据库分表分库

什么是分库分表

在我们的项目发展到一定阶段之后，随着数据量的增大，分库分表就变成了一件非常自然的事情，分表分库就是将存放在一台数据库服务器中的数据，按照特定方式进行拆分，分散存放到多台数据库服务器中，以达到分散单台服务器负载的效果

Mycat概述

MyCat是目前最流行的基于Java语言编写的数据库中间件，是一个实现了MySql协议的服务器，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。

Mycat详解

Mysql服务器企业实战

@1=5

@2='test'

UPDATE oldboy.student

WHERE

@1=6

@2='oldgirl1'

SET

@1=6

@2='test'

at 500

*** INNODB

*** innodb_force_recovery =4

*** Replication Slave

UPDATE `oldboy`.`student`