日志:179 评论:30
北京, 西城
【MySQL】MySQL学习笔记

准备工作

本地化

如果是新环境,我们需要设置时区以保证时间显示正确

timedatectl set-timezone Asia/Shanghai

安装wget

如果环境里没有wget,通过yum安装一下

yum -y install wget

安装gcc

如果环境里没有编译工具,通过yum安装一下

yum -y install gcc gcc-c++ make

安装依赖包

yum -y install libaio numactl-libs

建立环境根目录

mkdir -p /tongfu.net/env/

建立安装包目录并进入

mkdir /packages
cd /packages

安装MySQL 5.7

准备

mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

下载安装包

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

创建账号

useradd mysql

安装mysql

tar xzvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /tongfu.net/env/
cd /tongfu.net/env/
mv mysql-5.7.22-linux-glibc2.12-x86_64 mysql-5.7.22

初始化

mkdir mysql-5.7.22/data/
chown -R mysql.mysql mysql-5.7.22/
./mysql-5.7.22/bin/mysqld --initialize --user=mysql --basedir=/tongfu.net/env/mysql-5.7.22/ --datadir=/tongfu.net/env/mysql-5.7.22/data/

注意:执行完上面的命令后,会有初始化密码打印出来,一定记得保存起来

2018-07-25T23:47:56.430639Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-07-25T23:47:57.762501Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-07-25T23:47:57.891333Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-07-25T23:47:58.002282Z 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: 287aa3d9-9065-11e8-93c5-02420a100164.
2018-07-25T23:47:58.003611Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-07-25T23:47:58.004209Z 1 [Note] A temporary password is generated for root@localhost: [初始化密码]

启动

修改启动脚本

[root@tongfunet]# vi ./mysql-5.7.22/support-files/mysql.server

basedir=/tongfu.net/env/mysql-5.7.22/

datadir=/tongfu.net/env/mysql-5.7.22/data/

启动服务

./mysql-5.7.22/support-files/mysql.server start

修改默认密码

使用前面得到的初始化密码修改root密码

./mysql-5.7.22/bin/mysqladmin -uroot -p password "abcdef"

登录

./mysql-5.7.22/bin/mysql -uroot -pabcdef

自动启动

添加自动启动脚本

[root@tongfunet]# cat > /lib/systemd/system/mysqld.service <<EOF
[Unit]
Description=mysqld
After=network.target

[Service]
Type=forking
ExecStart=/tongfu.net/env/mysql-5.7.22/support-files/mysql.server start
ExecReload=/tongfu.net/env/mysql-5.7.22/support-files/mysql.server restart
ExecStop=/tongfu.net/env/mysql-5.7.22/support-files/mysql.server stop
PrivateTmp=true

[Install]
WantedBy=multi-user.target
EOF

使用 systemctl 管理 mysqld 服务

systemctl enable mysqld # 设置自动启动

systemctl start mysqld # 启动服务

systemctl stop mysqld # 停止服务

systemctl restart mysqld # 重启服务

常见错误1

在新版本的mysql里是找不到my.cnf配置文件的,因为mysql把大部分参数都设置了默认值,原则上我们不需要设置太多参数

如果想要设置的话,可以从旧版本的mysql环境下复制过来,放到mysql的安装目录下

当然,你也可以像以前那样放到 /etc/my.cnf,不过,这样不利用管理和迁移

常见错误2

在新版本的mysql里使用group by语句的时候有了限制,简单说就是group by的字段必须在select内出现,这样的话select * from xxx group by yyy就行不通了。

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方法,在my.cnf里增加一行设置即可

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

这样就可以随便写了~

常见错误3

新版本的mysql在做同步复制的时候,发现server_id改了没有变化,始终是4294967295,怎么回事?

经过各自尝试发现,原理新版本的mysql对于server_id的位数做了限制,数字不能超过10位,否则就会溢出了

TNND,能不能向后兼容啊~~

常见问题1

如果在导入sql的时候发现写入速度非常慢,可以从以下几个方面检查

  • 检查 max_allowed_packet 是否设置太小了,这个会影响大sql的写入速度,一般情况下dump出来的sql都是多行用一个insert语句执行写入的

  • 检查是不是使用的 innodb 引擎的数据表,innodb 数据表写和读都要比 myisam 慢一些

  • 检查是不是开启了 log-bin,这玩意开启的话导入速度会慢几百倍,上千倍,强烈建议导入数据时候关闭它!!

同步复制(Replication)

主从同步复制

概念

所谓主从同步就是建立两个mysql服务器,一个负责写和读(主),一个只负责读(从)

每次主mysql写入了新数据,从mysql会自动将数据变化同步到自己上

从而达到了主mysql的任何变化都会被自动时时同步到从mysql上

用途

一般情况下,主从同步模式的目的是为了减少数据库的读压力推出的解决方案

或者还有用主从同步模式来进行数据备份,毕竟dump数据库的数据是需要锁表的嘛

配置

主mysql配置(IP地址192.168.1.100)

server_id=131415001
log-bin=mysql-bin
binlog-do-db=test
expire_logs_days=10
max_binlog_size=1G
binlog-format=row

注意事项

  • server_id:要保证全局(也就是你的服务器所在的网络下)唯一

  • binlog-do-db:我们要复制那个数据库就写那个,强烈建议不要复制多个库

  • expire_logs_days:日志保留天数,为了防止日志文件占用过多磁盘空间,我们可以设置为最多保留10天的日志

  • max_binlog_size:单个日志文件最大尺寸,1G是比较合适的大小

  • binlog-format:日志格式,推荐使用row

从mysql配置(IP地址192.168.1.200)

server_id=131415002
log-bin=mysql-bin
binlog-do-db=test
expire_logs_days=10
max_binlog_size=1G
binlog-format=row

从mysql的uuid要和主mysql区别开

[root@tongfunet]# cat /tongfu.net/env/mysql-5.7.22/data/auto.cnf
[auto]
server-uuid=b32339d5-3bd7-11e9-0002-0242c0a801d2

准备工作

(在主机操作)在主机授权从机的同步复制权限

在hosts里设置主机头

192.168.1.200 sync_slave1

在mysql里执行

grant replication slave, replication client on *.* to sync_user@sync_slave1 identified by 'sync_pass';

(在从机操作)在从机停止同步复制,并且设置master为主机

在hosts里设置主机头

192.168.1.100 sync_master

在mysql里执行

stop slave;
change master to master_host = 'sync_master', master_port = 3306, master_user = 'sync_user', master_password = 'sync_pass';

操作步骤

(在主机操作)从主机导出数据库数据

mysqldump -uroot -pabcdef test > test-utf8-190302.sql --default-character-set=utf8 --extended-insert=false --master-data --lock-all-tables -h 192.168.1.100

(在从机操作)在从机建立空数据库test(如果数据库test已经存在就先drop掉再重新create一遍),注意编码

drop database if exists test;
create database test default charset utf8;

(在从机操作)在从机导入刚刚从主机导出的数据库sql文件

强烈建议导入sql文件时候先把从机的my.cnf里的log-bin先注释掉,重启一下mysql再操作,否则导入速度会让你抓狂~~

mysql -uroot -pabcdef test < test-utf8-190302.sql --default-character-set=utf8 -h 192.168.1.200

如果在导入sql文件之前注释了log-bin一定记得要放开注释,重启一下mysql再进行后面的操作!

(在从机操作)开启主机到从机的同步复制

start slave;

这里就可以了!!!

现在主机改动数据从机就可以立即同步到变化了。

主主同步复制

概念

所谓主主同步就是建立两个mysql服务器,彼此之间不分主从,都负责写和读

无论从任何一个mysql上进行写操作,都会自动实时地同步到另外一个mysql上

用途

主主同步复制常用于服务器热切需求的环境

因为任何一个mysql坏掉了,可以立即切换到另外一个mysql上使用

服务不受任何影响,且无需做任何额外的操作

配置

主mysql一配置(IP地址192.168.1.100)

server_id=131415001
log-bin=mysql-bin
binlog-do-db=test
expire_logs_days=10
max_binlog_size=1G
binlog-format=row
auto_increment_increment=2
auto_increment_offset=1
slave-skip-errors=all

主义事项

  • auto_increment_incrment:自增列的数字增长数,一次增加2个

  • auto_increment_offset:自增列的数字增长基数,这里设置为奇数

主mysql二配置(IP地址192.168.1.200)

server_id=131415002
log-bin=mysql-bin
binlog-do-db=test
expire_logs_days=10
max_binlog_size=1G
binlog-format=row
auto_increment_increment=2
auto_increment_offset=2
slave-skip-errors=all

注意事项

  • auto_increment_offset:自增列的数字增长基数,这里设置为偶数

主mysql二的uuid要和主mysql一区别开

[root@tongfunet]# cat /tongfu.net/env/mysql-5.7.22/data/auto.cnf
[auto]
server-uuid=b32339d5-3bd7-11e9-0002-0242c0a801d2

准备工作

(在192.168.1.100操作)在主一授权主二的同步复制权限
在hosts里设置主机头

192.168.1.200 sync_master

在mysql里执行

grant replication slave, replication client on *.* to sync_user@sync_master identified by 'sync_pass';

(在192.168.1.200操作)在主二授权主一的同步复制权限
在hosts里设置主机头

192.168.1.100 sync_master

在mysql里执行

grant replication slave, replication client on *.* to sync_user@sync_master identified by 'sync_pass';

(在192.168.1.100操作)在主一停止同步复制,并且设置master为主二

stop slave;
change master to master_host = 'sync_master', master_port = 3306, master_user = 'sync_user', master_password = 'sync_pass';

(在192.168.1.200操作)在主二停止同步复制,并且设置master为主一

stop slave;
change master to master_host = 'sync_master', master_port = 3306, master_user = 'sync_user', master_password = 'sync_pass';

操作步骤

(在192.168.1.100操作)从主一导出数据库数据

mysqldump -uroot -pabcdef test > test-utf8-190302.sql --default-character-set=utf8 --extended-insert=false --master-data --lock-all-tables -h 192.168.1.100

(在192.168.1.200操作)在主二建立空数据库test(如果数据库test已经存在就先drop掉再重新create一遍),注意编码

drop database if exists test;
create database test default charset utf8;

(在192.168.1.200操作)在主二导入刚刚从主一导出的数据库sql文件

强烈建议导入sql文件时候先把从机的my.cnf里的log-bin先注释掉,重启一下mysql再操作,否则导入速度会让你抓狂~~

mysql -uroot -pabcdef test < test-utf8-190302.sql --default-character-set=utf8 -h 192.168.1.200

如果在导入sql文件之前注释了log-bin一定记得要放开注释,重启一下mysql再进行后面的操作!

(在192.168.1.200操作)开启主一到主二的同步复制

start slave;

(在192.168.1.200操作)查看主二的日志文件和位置

MySQL> flush logs;
Query OK, 0 rows affected (0.63 sec)

MySQL> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 | test         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

(在192.168.1.100操作)在主一设置主二的日志文件和位置

change master to master_log_file = 'mysql-bin.000002', master_log_pos = 154;

(在192.168.1.100操作)开启主二到主一的同步复制

start slave;

这里就可以了!!!

现在主二改动数据主一立即就可以同步到变化,反过来主一改动数据主二也可以立即同步到变化了。

同步复制重置

同步复制重置

介绍

有些时候同步复制slave无法启动了,这个时候我们就需要进行一次修复操作

操作步骤

查看当前slave状态

show slave status;

重置slave状态

reset slave;

手动设置slave状态(设置成第一步看到的同步状态)

change master to master_log_file = '...', master_log_pos ...;

启动slave

start slave;

配置文件详解

优化参数

key_buffer_size

索引块的缓冲区大小,对MyISAM表性能影响最大的一个参数.决定索引处理的速度,尤其是索引读的速度。默认值是16M,通过检查状态值Key_read_requests

key_buffer_size = 32M

max_allowed_packet

一个查询语句包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节,但是可在需要时增加到max_allowed_packet个字节。

max_allowed_packet = 256M

sort_buffer_size

是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。

如果不能,可以尝试增加sort_buffer_size变量的大小。

sort_buffer_size = 16M

read_buffer_size

是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。

如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。

read_buffer_size = 16M

join_buffer_size

应用程序经常会出现一些两表(或多表)Join的操作需求,MySQL在完成某些 Join 需求的时候(all/index join),为了减少参与Join的“被驱动表”的

读取次数以提高性能,需要使用到 Join Buffer 来协助完成 Join操作。当 Join Buffer 太小,MySQL 不会将该 Buffer 存入磁盘文件,而是先将Join Buffer中的结果集与需要 Join 的表进行 Join 操作,然后清空 Join Buffer 中的数据,继续将剩余的结果集写入此 Buffer 中,如此往复。这势必会造成被驱动表需要被多次读取,成倍增加 IO 访问,降低效率。

join_buffer_size = 16M

max_connections

MySQL无论如何都会保留一个用于管理员(SUPER)登陆的连接,用于管理员连接数据库进行维护操作,即使当前连接数已经达到了max_connections。

max_connections = 500

max_connect_errors

max_connect_errors是一个MySQL中与安全有关的计数器值,它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。max_connect_errors的值与性能并无太大关系。

当此值设置为10时,意味着如果某一客户端尝试连接此MySQL服务器,但是失败(如密码错误等等)10次,则MySQL会无条件强制阻止此客户端连接。

max_connect_errors = 1000

table_open_cache

表描述符缓存大小,可减少文件打开/关闭次数;

table_open_cache = 1024

open_files_limit

mysql打开最大文件数

open_files_limit = 65535