mysql 8.0.22安装linux及主从搭建

一、软件下载与安装
1.mysql官网下载二进制文件，上传服务器，解压
2.参考官网安装手册 doc
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar xvf /path/to/mysql-VERSION-OS.tar.xz
shell> ln -s full-path-to-mysql-VERSION-OS mysql  #此步改为mv mysql-8.0.22-linux-glibc2.12-x86_64 mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql       #记住此执行出来的密码 
2020-12-09T07:15:35.971853Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-12-09T07:15:35.971977Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.22) initializing of server in progress as process 1430
2020-12-09T07:15:36.000042Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-09T07:15:37.034094Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-09T07:15:37.903144Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: C0tAm:aX;Ui&
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &     #开启mysql服务
2020-12-09T07:16:59.497136Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql  #mysql可以自己选择命名,mysql会配置开机自动启动

3.修改/etc/my.cnf文件
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0 #MySQL数据库及表(仅MyISAM)支持符号链接(symbolic link)，即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录。要支持符号链接，需要在配置中设置symbolic-links=1(较新的版本为默认开启)
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password  #使用以前老版本的加密模式，如果不使用，很多如mycat,hive等链接过来都会包密码错误

port=3306
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

slow_query_log=1  #开启慢sql查询日志
slow_query_log_file=/var/lib/mysql/master-slow.log #开启慢sql查询日志文件
long_query_time=3  #定义慢sql时间，单位秒

log_bin_trust_function_creators=1 #简单介绍一下，当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。
                                  #如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 
                                  #设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 
                                  #此变量也适用于触发器的创建。

log-bin=mysql-bin #主从复制要开启二进制文件
server-id=1  #主从复制要指定一个id
binlog-ignore-db=mysql #不要同步mysql库 #也可以配置 binlog-do-db=dbname 要配置同步的库名称
binlog-format=MIXED  #默认是STATEMENT #设置二进制文件格式；如果是STATEMENT时并不安全，事务隔离级别为 READ-COMMITTED的InnoDB引擎，配置hive时会报错

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

3.启动mysql服务
/etc/init.d/mysql.server start
root       1724      1  0 17:02 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/master.pid
mysql      1888   1724 16 17:02 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/lib/mysql/master.pid --port=3306

登录mysql更改密码
/usr/local/mysql/bin/mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'yehaver';  #刚安装完只能使用此语句修改密码
use mysql;
update user set host = '%' WHERE user='root';
flush privileges;
ALTER USER 'root'@'%' IDENTIFIED BY 'yehaver';   #是提供远程登录
flush privileges;  #每次权限更新都要刷新

4.安装报错修改
2020-12-09T07:16:59.497136Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
mkdir /var/log/mariadb
touch /var/log/mariadb/mariadb.log
chown -R mysql:mysql /var/log/mariadb/

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
vim /etc/my.cnf
#socket=/var/lib/mysql/mysql.sock  #注释此，sock模式不让本地登录

5.设置mysql自启动
chkconfig mysql.server on  #mysql.server文件是你复制 /etc/init.d/下的文件名cp support-files/mysql.server /etc/init.d/mysql.server。可以换不同文件名
chkconfig  --list  #检查自启动是否生效
systemctl status mysql.server

6.设置环境变量
vi /root/.bash_profile
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin
source /root/.bash_profile

7.启动停止mysql
systemctl stop mysql.service
systemctl start mysql.service
systemctl status mysql.service

二、mysql优化
explain select * from t_user; #生成执行计划
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | primary     | t2          | NULL       | range| NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
|  2 | simple      | t1          | NULL       | index| NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
|  3 | derived     | t3          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
|  1 | union       | <derived3>  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
总结：
1.id越大，mysql最先执行；id相同，排在最上面的最先执行
2.t3会derived先执行出来的虚拟表，然后<derived3>为虚拟表再分析执行

select_type有如下类型
SIMPLE        简单表，不使用表连接或子查询
PRIMARY        主查询，即最外层的查询
SUBQUERY    子查询中的第一个
derived     派生表,由其它子查询产生，有可能会生成临时表
UNION        UNION中的第二个或者后面的查询语句
UNION RESULT union结果集
DEPENDENT SUBQUERY 在select 或where列表中包含了子查询，子查询基于外层
uncacheable subquery  不可用缓存的子查询，使用了系统变量

partitions：如果查询的有表分区，会命中分区名

type有如下类型
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
system  表只有一条记录
const   索引扫描一次；主键索引和唯一索引找到一条记录
eq_ref  唯一索引扫描;主键索引和唯一索引关键表
ref     索引扫描;返回关联表一条或多条记录的关键表
range   索引范围扫描；条件中有 >=  <= > < between and in (x1,x2)
index   full index scan;索引全扫描，查询全表且只查索引字段
all     全表扫描
index_merge  合并索引，col1 is null or col2 = 1;
ref or null  既需要关联也需要 null
index_subquery  利用索引来关联
unique_subquery 子查询中用到唯一索引

possible_keys  查询涉及到的字段存在多个索引，则索引被列出，但不一定被使用；显示用到的索引名
key          实际被使用的索引。  如使用了覆盖索引，则该列出现在key列中；显示用到的索引名
key_len      索引使用的字节数，此值越大越好
ref           索引被使用的情况；可能是一个常量，可能是关联id条件
rows          每张表有多少条数据被读取
filtered    返回的数据在server层过滤后，剩下满足查询记录数的比例

Extra         其它需要展示的重要信息
  using filesort  外部索引排序
  using temporary 使用了临时表保存中间结果
  using index     使用了覆盖索引；覆盖索引：查询的字段正好是索引的字段，就会使用覆盖索引；特殊案例：当查询条件用like '%abc%'时，查询字段指定为索引字段可走索引扫描
  using where       使用了条件
  using join buffer 使用了连接缓存
  impossible where 条件中本身就是fasle,一般为 where 1=2
  select tables optimized away   myisam已经保存了记录的总数等信息，直接返回结果，，而innodb还需要全表扫描。
  distinct            找到第一匹配的元组后立即停止找到相同的值

sort_buffer_size : 如果在执行计划中有排序ORDER BY ,且出现排序缓冲区不够写临时文件时，可能需要调大此参数
max_length_for sort_data: 执行计划中有排序，且查询的字段又太多，这个也需要调大

2.开启mysql慢日志信息  mysqldumpslow
show variables like '%slow_query_log%'; #查看日志是否开启，仅本次生效，下次重启后失效
show variables like '%long_query_time%'; #定义慢sql时间，单位秒
show global status like '%slow_queries%';#查询系统慢sql数量
 
set global slow_query_log = 1;
set global long_query_time = 3;  #修改后重新链接才看得到
set global log_bin_trust_function_creators = 1;
永久生效方法
在my.cnf文件中,[mysqld]下面新增如下参数。然后重启mysql服务
slow_query_log=1  #开启慢sql查询日志
slow_query_log_file=/var/lib/mysql/master-slow.log #开启慢sql查询日志文件
long_query_time=3  #定义慢sql时间，单位秒
log_bin_trust_function_creators=1 #简单介绍一下，当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。

mysqldumpslow --help可显示其参数的使用
-s，是order的顺序
al   平均锁定时间
ar   平均返回记录时间
at   平均查询时间（默认）
c    计数
l    锁定时间
r    返回记录
t    查询时间
-t，是top n的意思，即为返回前面多少条的数据
-g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s t -t 10 -a slow.log  #常用，其它请百度

3.开启mysql慢sql资源步骤分析profiling
show variables like '%profiling%'; #查看profiling日志是否开启
set profiling=1; #此只在当前会话开启，用来分析当条sql慢，dba来研究分析。后面所有查询的sql都会记录详细资源使用情况
sql语句。。。
show profiles; #展示上面写的sql语句执行情况
show profile for query xx_id  #详细展示上面某条sql资源使用情况 show profile for query 3;
show profile cpu,block io for query 3; #增加了cpu，io使用情况

4.全局查询日志  此用到比较少，生产不要开启此功能。略

5.mysql锁机制
表锁：myisam
unlock tables; 解锁所有表
lock tables 表名 read或者write #给指定的表加读锁或写锁
show open tables; #展示所有锁住的表

Mylsam引擎在执行查询语句（select）前，会自动涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MYSQL的表级锁有两种模式:
1.表共享读锁（Table Read Lock）
2.表共享写锁（Table Write Lock）

table_locks_immediate:表示立即获取锁的查询次数，没获取一次加1
table_locks_waited:表示表级锁争用而发生等待的次数，每等待一次加1

Table_locks_immediate表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，
如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，
因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。
示例中的服务器Table_locks_immediate / Table_locks_waited ＝ 235，MyISAM就足够了。

行锁：innoDB
select * from table_name where a.col1=8 for update; #锁定一行

show status like '%innodb_row_lock%'  #查询行锁情况
Innodb_row_lock_current_waits:当前正在等待锁的数量；
Innodb_row_lock_time:从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度；
Innodb_row_lock_waits:系统启动到现在总共等待的次数；

6.杀掉锁的进程
show processlist;  #查询mysql所有运行的进程
kill @Id;  #杀掉上面查询出来的某个id

三、主从复制
主机
1.my.cnf文件配置
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-ignore-db=mysql #不要同步mysql库 #也可以配置 binlog-do-db=dbname 要配置同步的库名称
binlog-format=STATEMENT #设置二进制文件格式   STATEMENT,ROW,MIXED具体百度意思;都是直接使用MIXED
2.CREATE USER 'repl'@'从机ip' IDENTIFIED WITH mysql_native_password BY '密码';
  GRANT REPLICATION SLAVE ON *.* TO '主机用户'@'从机ip';
3.flush privileges;
4.show master status;

从机
先按照软件下载与安装重新搭建一个mysql
1.my.cnf配置
[mysqld]
log-bin=mysql-bin  #从机可选
server-id=2  #和上面要不一样
relay-log=s1-relay-bin  #同步日志文件名 默认是使用 主机名来定义的，当你的服务器作为slave并且更改主机名的时候，同步会被停止。如果你一直没有改主机名就无所谓
2.登录mysql,配置从机同步
/usr/local/mysql/bin/mysql -u root -p
change master to master_host='主机ip',master_user='repl',master_password='密码',master_log_file='binlog.000009',master_log_pos=156;   
#master_log_pos 主机show master status的Position值   #master_log_file 主机 show master status的file值
3.start slave;
4.show slave status\G  #slave_io_running=yes和slave_sql_running=yes才叫同步成功

上述只能同步新数据库里面的东西，历史库需要手动同步
1.主库导出db_user数据库
FLUSH TABLES WITH READ LOCK;  #锁定主数据库
SHOW MASTER STATUS;  #查询主数据库状态，并记下FILE及Position的值
./mysqldump -uroot -p --databases db_user > db_user.sql  #退出mysql终端，执行mysql备份命令
scp db_user.sql 'root'@'172.190.0.0:/opt/data'  # 复制到另外一台机器, 并输入对应的密码

2.从库同步历史库数据
# 在导入备份文件之前，需要在从库中手动建立相应的同名库
CREATE DATABASE test;
mysql -uroot -p < db_user.sql
stop slave; #如果主从备份开启先停止
#之后再次配置上面的主从备份 
...
start slave;
show slave status\G
select * from mysql.slave_master_info \G  #查看主从配置参数
 
3.主库解锁
unlock tables;
测试历史库表插入数据是否在备库中已经有了