MySQL8.0安装详解

一、安装系统配置

到公有云上申请一个ECS，测试使用，规格可以随意，磁盘空间100G。

操作系统镜像：CentOS7.4

内核版本： 3.10.0-327.36.3.el7.x86_64

MySQL8.0各个安装包的用途。

需要到官网下载  https://dev.mysql.com/downloads/mysql/

mysql-8.0.14-1.el7.x86_64.rpm-bundle.tar

二、安装MySQL8.0

1. 准备工作

                a. 先创建分区

                                fdisk /dev/vdb

                                创建出两个分区，一个70G用于存在mysql数据，一个30G，用于放软件等资料。

                b. 格式化文件系统：

                                mkfs.ext4 /dev/vdb1

                                mkfs.ext4 /dev/vdb2

                c. 创建挂载点

                                mkdir -p /var/lib/mysql

                                mkdir -p /var/software

                                echo '/dev/vdb1 /var/lib/mysql ext4 rw,nosuid,nodev,exec,auto,nouser,async 0 0' >> /etc/fstab

                                echo '/dev/vdb2 /var/software ext4 rw,nosuid,nodev,exec,auto,nouser,async 0 0' >> /etc/fstab

                                mount -a

2. 上传Mysql安装包

                mysql-8.0.14-1.el7.x86_64.rpm-bundle.tar

3. 解压安装包和卸载冲突的安装包

                tar -xvf mysql-8.0.14-1.el7.x86_64.rpm-bundle.tar

4. 安装

                a. rpm -ivh mysql-community-common-8.0.14-1.el7.x86_64.rpm

b.卸载冲突包      

rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps

                c. rpm -ivh mysql-community-libs-8.0.14-1.el7.x86_64.rpm

                d. rpm -ivh mysql-community-client-8.0.14-1.el7.x86_64.rpm

                e. rpm -ivh mysql-community-devel-8.0.14-1.el7.x86_64.rpm --nodeps

5. 创建用户和组

                groupadd mysql

useradd mysql -g mysql

6. 创建目录

                mkdir -p /var/lib/mysql/tmp

                chown mysql:mysql /var/lib/mysql/tmp

                mkdir -p /var/lib/mysql/data

                chown mysql:mysql /var/lib/mysql/data

7. 配置参数文件

                vi /etc/my.cnf

                # The following options will be passed to all MySQL clients

[client]

#password = your_password

port = 3306

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

# The MySQL server

[mysqld]

port = 3306

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

datadir = /var/lib/mysql/data

skip-external-locking

default-storage-engine = INNODB

key_buffer_size = 256M

max_allowed_packet = 1G

table_open_cache = 4096

sort_buffer_size = 8M

read_buffer_size = 8M

read_rnd_buffer_size =32M

myisam_sort_buffer_size = 64M

thread_cache_size = 32

#query_cache_size= 16M  #unknown variable

#query_cache_type=0     #unknown variable

event_scheduler=1

skip-name-resolve

skip-slave-start

max_connections=2000

character_set_server=utf8

user=mysql

log-error = /var/lib/mysql/data/error.log

# Try number of CPU's*2 for thread_concurrency

#thread_concurrency = 8

lower_case_table_names = 1

join_buffer_size =2M

transaction_isolation=READ-COMMITTED

innodb_flush_method=O_DIRECT

#sql_mode=ANSI,TRADITIONAL,STRICT_ALL_TABLES

explicit_defaults_for_timestamp=false

local-infile=1

#skip-networking

#secure_auth = ON   # unknown variable

skip-symbolic-links

#sql_mode = NO_AUTO_CREATE_USER

log_bin_trust_function_creators =1

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

log-bin-index=mysql-bin.index

slave_parallel_type=LOGICAL_CLOCK                             #for MTS

slave_parallel_workers=32

#open log_slow_queries sync_binlog 2011-9-24 xzlox

long_query_time = 10

slow-query-log = 1

log_output = file

sync_binlog=0

# binary logging format - row recommended

binlog_format=mixed

slave-skip-errors=all

relay-log=relay-bin

relay-log-index=relay-bin.index

expire_logs_days=1

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 611

# Uncomment the following if you are using InnoDB tables

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /var/lib/mysql/data/

innodb_log_files_in_group = 5

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 1000M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 256M

innodb_log_buffer_size = 16M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

#replicate-do-db = ym

innodb_file_per_table

# GTID

log_slave_updates=1

gtid-mode=on

enforce-gtid-consistency=true

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

8. 安装server端

            安装依赖包：

            yum install perl

                rpm -ivh mysql-community-server-8.0.14-1.el7.x86_64.rpm

9. 关闭seLinux和防火墙 (有些操作系统需要这一步)

                setenforce 0

                并且/etc/selinux/config将enforcing设为disabled

                iptables -X

    iptables -F

10. 初始化数据并启动mysql

                mysqld --initialize --basedir=/usr/ --datadir=/var/lib/mysql/data --user=mysql

                /bin/systemctl start  mysqld.service

11. 找到临时密码

                cat /var/lib/mysql/data/error.log 

    ([Note] A temporary password is generated for root@localhost: iZ6BTu+&AXbh)

12. 修改密码

            set sql_log_bin=0;

 ALTER USER 'root'@'localhost' IDENTIFIED BY '******';

 flush privileges;

13. 停止mysql

                /bin/systemctl stop  mysqld.service

14. 连接数据库

                mysql -uroot -p -A

二、配置半同步

1. 查看是否支持动态加载，需要下面的变量设为YES

                mysql> show variables like 'have_dynamic_loading';

                +----------------------+-------+

                | Variable_name        | Value |

                +----------------------+-------+

                | have_dynamic_loading | YES   |

                +----------------------+-------+

                1 row in set (0.01 sec)

2. 在主从库分别按照插件, 考虑到后面主从倒换，需要两边都安装

                主库：

                INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

                从库：

                INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

3. 查看是否正常

             mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

                +----------------------+---------------+

                | PLUGIN_NAME          | PLUGIN_STATUS |

                +----------------------+---------------+

                | rpl_semi_sync_master | ACTIVE        |

                +----------------------+---------------+

                1 row in set (0.00 sec)

4. 建立主从同步

                a.  创建root账号和复制账号repl并赋权，主从库都执行

                                set sql_log_bin=0;

                                CREATE USER 'repl'@'%' IDENTIFIED BY '******';

                                GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

                                create user root@'%' identified by '******';

                                grant all on *.* to root@'%';

                                flush privileges;

                b. 获取主库的binlog

                                mysql> show master status\G

                                *************************** 1. row ***************************

                                                                                 File: mysql-bin.000008

                                                                 Position: 611

                                                 Binlog_Do_DB:

                                 Binlog_Ignore_DB:

                                Executed_Gtid_Set:

                                1 row in set (0.00 sec)

                c. 建立主从关系

                                CHANGE MASTER TO

                                                MASTER_HOST='10.**.**.50',

                                                MASTER_PORT=3306,

                                                MASTER_USER='repl',

                                                MASTER_PASSWORD='******',

                                                MASTER_LOG_FILE='mysql-bin.000008',

                                                MASTER_LOG_POS=611,

                                                MASTER_AUTO_POSITION=0;

                                开通了gtid的话，用下面的方法：

                                CHANGE MASTER TO

                                                MASTER_HOST='10.**.**.50',

                                                MASTER_PORT=3306,

                                                MASTER_USER='repl',

                                                MASTER_PASSWORD='Gauss_234',

                                                MASTER_AUTO_POSITION=1;                                          

这个时候不要忘记在主库上将防火墙关闭；如果没有telnet可以用nc -v $IP 3306命令进行端口探测

                d. 在主从库上添加相关变量

                                主库：

                                set global rpl_semi_sync_master_enabled=1;

                                set global rpl_semi_sync_master_timeout=1000;

                                从库：

                                set global rpl_semi_sync_slave_enabled=1;

                                为了保证这些变量启动实例后生效，需要将他们放到配置文件中：

                                主库:

                                rpl_semi_sync_master_enabled=1

                                rpl_semi_sync_master_timeout=1000

                                从库:

                                rpl_semi_sync_slave_enabled=1

rpl_semi_sync_master_timeout=1000

                                另外：rpl_semi_sync_master_wait_point这个变量可以取值AFTER_SYNC(默认) 和 AFTER_COMMIT：这个值是采用老式的MySQL5.5/5.6半同步复制工作

                                一个重点需要监控的状态变量为：Rpl_semi_sync_master_status， Rpl_semi_sync_slave_status

                                show status like 'Rpl_semi_sync_master_status';

                                show status like 'Rpl_semi_sync_slave_status';

5. 启动同步

start slave;

show slave status\G