运维相关之MySQL主从搭建

简介：MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一
集群模式：
    1) Mysql主从架构
    2) MHA 架构
    3) MMM 架构
    4) DRBD 架构

主从安装步骤：
    #以下步骤在所有集群服务器上都要做
    1.删除原有的mariadb
    查询:  rpm -qa|grep mariadb
    卸载:  rpm -e --nodeps mariadb-libs
    
    2.在https://dev.mysql.com/downloads/mysql/选择为Red Hat Enterprise Linux 7 / Oracle Linux 7 ，把os的版本选择为all。
    直接下载mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar，所有的rpm包都在里面
    解压安装：
    tar -xvf mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar
    解压后按顺序安装：
    rpm -ivh mysql-community-common-5.7.21-1.el7.x86_64.rpm
    rpm -ivh mysql-community-libs-5.7.21-1.el7.x86_64.rpm
    rpm -ivh mysql-community-devel-5.7.21-1.el7.x86_64.rpm
    rpm -ivh mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm
    rpm -ivh mysql-community-client-5.7.21-1.el7.x86_64.rpm
    rpm -ivh mysql-community-server-5.7.21-1.el7.x86_64.rpm
    
    3.修改配置文件，vim /etc/my.cnf
    [mysqld]
    #max_allowed_packet 参数限制
    max_allowed_packet = 100M
    # 此参数必须要设置，建议为总内存的70%，否则很容易挂掉的。
    innodb_buffer_pool_size = 1024M
    # 1 ：不区分大小写，0：大小写敏感，Linux系统必须设置
    lower_case_table_names = 1
    # sql_mode 用于MySQL的校验严格（还是宽松）
    # ONLY_FULL_GROUP_BY:   严格校验Group By语法
    # STRICT_TRANS_TABLES:  在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
    # PIPES_AS_CONCAT:   将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似
    sql_mode=ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    # 关闭自动提交，启用事务  0是开启事务 1关闭事务
    autocommit=0
    # 开启归档(binlog)日志
    log_bin=ON
    log_bin_basename=/var/lib/mysql/mysql-bin
    log_bin_index=/var/lib/mysql/mysql-bin.index
    # 超过30天的binlog删除
    expire_logs_days = 30
    # 最大连接数
    max_connections = 1000
    # 限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300
    innodb_open_files = 600
    # ---------- 以上内容必须配置 ------------------
    # ---------- 以下内容选配 ----------------------
    # 记录慢查询日志
    slow_query_log = 1
    long_query_time = 5 # 慢查询时间 超过5秒则为慢查询
    slow_query_log_file = /data/mysql/mysql-slow.log
    # 排序规则
    collation_server=utf8_general_ci
    # 数据库字符集
    character-set-server=utf8
    # 超时时间设定为21天
    wait_timeout=1814400    
    
    4.设置mysql开机自启
    systemctl enable mysqld
    systemctl start mysqld
    
    5.首次登陆MySQL
    #安装后第一次登陆MySQL，会自动生成密码，可在MySQL日志文件搜索password关键字得到密码
    mysql -uroot -p密码
    #设置密码的验证强度等级,设置为 最低6 位的密码
    mysql> set global validate_password_policy=LOW;
    mysql> set global validate_password_length=6;
    #修改root密码
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
    
    6.添加远程登录用户
    #默认只允许root帐户在本地登录，如果要在其它机器上连接mysql，必须修改root允许远程连接，或者添加一个允许远程连接的帐户，为了安全起见，添加一个新的帐户
    mysql> GRANT ALL PRIVILEGES ON *.* TO '新用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
    
    7.配置主从集群
    #主库配置文件
    vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin
    server-id=101
    
    #备库配置文件
    vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin
    server-id=102
    
    8.主库创建同步用户
    mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
    mysql> GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '12345678';
    # show master status查看主库状态
    
    9.备库设置同步
    mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
    mysql> change master to master_host='192.168.56.101',master_user='backup',master_password='12345678',master_log_file='mysql-bin.000003',master_log_pos=2500;    
    # master_host为docker的地址不能写127.0.0.1 master_user是在主库创建的用户 master_log_pos是主库show master status;查询出的Position
    mysql> start slave; #启动备库
    
    10.查看服务状态
    mysql> show slave status\G
    # Waiting for master to send event 就是成功了
    # Connecting to master 多半是连接不通
    # 之后主库的修改都能同步到从库了
    
    11.开启慢查询日志记录
    [mysqld]
    slow_query_log = ON  #开启慢查询
    slow_query_log_file = /usr/local/mysql/data/slow.log  #日志文件地址
    long_query_time = 3  #超过3秒的就会记录
    log_queries_not_using_indexes=1      #记录没有索引的查询
    #配置后需要重新启动mysql，在主库上操作，重启主库即可，不用管备库
    #查看慢查询是否开启
    show variables  like '%slow_query_log%';
    
    12. 创建数据库、用户并授权
    CREATE DATABASE IF NOT EXISTS zjgcjs_v4 DEFAULT CHARACTER SET utf8;
    create user 'testdb'.* to 'test'@'%' identified by '1234';
    grant all privileges on 'testdb'.* to 'test'@'%' identified by '1234';
    
    13. mysql连接数
    #查看连接数，Threads_connected为当前已连接会话，Threads_running代表当前活动会话
    show status like '%connect%';
    show status like 'Threads%';
    show variables like '%max_connections%';
    #显示连接会话
    show processlist;
    
    14.mysql字符集
    #显示表的字符集，排序规则：_ci结尾表示大小写不敏感，_cs结尾表示大小写敏感
    SQL > SHOW CREATE TABLE runoob_ctl;
    #查看字段编码
    SHOW FULL COLUMNS FROM runoob_ctl;
    #把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集
    ALTER TABLE at_dict_group CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
    #查看数据库的排序规则
    SQL> SHOW VARIABLES LIKE 'collation\_%'
    #修改排序规则，vim /etc/my.cnf    
    [mysqld]
    # 排序规则
    collation_server=utf8_general_ci
    
    15.锁相关
    #查看锁超时时间，当前，全局
    mysql> SHOW VARIABLES LIKE "innodb_lock_wait_timeout";
    mysql> SHOW GLOBAL VARIABLES LIKE "innodb_lock_wait_timeout";
    #查看当前的事务,解锁 kill 线程号,kill trx_mysql_thread_id
    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
    #查看正在锁的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
    #查看等待锁的事务
    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    #查看表被锁状态
    show OPEN TABLES where In_use > 0; 
    #显示线程运行状态
    mysql> SHOW PROCESSLIST;
    
    16.缓存相关
    #查询缓存大小
    sql> show global variables like 'innodb_buffer_pool_size';
    #设置大小，MySQL5.7及以后版本，改参数时动态的，修改后，无需重启MySQL
    set global innodb_buffer_pool_size = 500M  #设置500M
    #配置文件修改
    [mysqld]
     innodb_buffer_pool_size = 500M