Willie Ramey
0%

Linux安装与配置MySQL8

发表于 分类于
本文字数： 10k 阅读时长 ≈ 9 分钟
  • Linux安装与配置
  • 增删改查
  • 主主、主从配置

MySQL

实验环境

1
2
3
4
系统版本：centos7.6 
版本及安装方式：mysql8 源码安装
配置：2核4G
源码安装依赖包：gcc 7.3 、cmake 3.14.5、mysql 8.0.26

准备工作

安装依赖包

1
2
3
4
5
6
7
8
9
yum -y install wget  make cmake gcc gcc-c++  ncurses  ncurses-devel \
libaio-devel  openssl openssl-devel autoconf bison automake zlib* \
fiex* libxml*  libmcrypt* libtool-ltdl-devel* 

// 补充：不想yun安装，源码编译的步骤
# tar -zxf bison-3.0.4.tar.gz -C /usr/src/ && cd /usr/src/bison-3.0.4/
# ./configure && make && make install
# tar -zxf cmake-3.14.5.tar.gz -C /usr/src/ && cd /usr/src/cmake-3.14.5/
# ./bootstrap && make && make install

下载源码包 (mysql-boost-8.0.26.tar.gz 此版本带有boost)

1
2
3
yum remove mariadb -y
wget https://github.com/Kitware/CMake/releases/download/v3.14.5/cmake-3.14.5.tar.gz
wget http://mirrors.sohu.com/mysql/MySQL-8.0/mysql-boost-8.0.26.tar.gz

升级gcc

yum默认安装gcc4.8.5 这里介绍使用scl软件集(Software Collections)实现多个gcc版本之间的灵活切换

1
2
3
4
5
yum -y install centos-release-scl
yum -y install devtoolset-7-gcc devtoolset-7-gcc-c++ devtoolset-7-binutils
scl enable devtoolset-7 bash
echo "source /opt/rh/devtoolset-7/enable" >>/etc/profile
gcc -v

源码编译安装cmake

1
2
3
4
5
6
7
8
9
yum默认安装2.8.12.2，编译mysql8.0.26会提示以下错误：
-- Running cmake version 2.8.12.2
CMake Warning at CMakeLists.txt:43 (MESSAGE):
  Please use cmake3 rather than cmake on this platform
-- Please install cmake3 (yum install cmake3)
CMake Error at CMakeLists.txt:55 (CMAKE_MINIMUM_REQUIRED):
  CMake 3.4.3 or higher is required.  You are running version 2.8.12.2
-- Configuring incomplete, errors occurred!
解决方法：更新cmake，先用yum移除原cmake
1
2
3
4
5
6
7
8
yum -remove cmake -y
tar -zxf cmake-3.14.5.tar.gz
cd cmake-3.14.5
./bootstrap
gmake && gmake install
/usr/local/bin/cmake  --version
cmake version 3.14.5
ln -s /usr/local/bin/cmake /usr/bin/

安装MySQL8.0.26

MySQL常见报错

解压、配置、编译、安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
tar -zxf mysql-boost-8.0.26.tar.gz && cd /local/server/mysql-8.0.26
mkdir build && cd build

cmake .. -DCMAKE_INSTALL_PREFIX=/local/server/mysql \
-DSYSCONFDIR=/local/server/mysql \
-DMYSQL_UNIX_ADDR=/local/webapp/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_0900_ai_ci \
-DWITH_SSL=system \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_INNODB_MEMCACHED=ON \
-DENABLED_LOCAL_INFILE=ON \
-DMYSQL_TCP_PORT=3306 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/tmp \
-DMYSQL_DATADIR=/local/webapp/data/mysql \
-DCMAKE_C_COMPILER=/opt/rh/devtoolset-7/root/usr/bin/gcc

编译过程中，如果写错或报错，需要删除保存配置信息的文件，再编译：rm -rf CMakeCache.txt

1
make -j 2 && make install

mysql的配置步骤

创建用户和组

1
groupadd mysql && useradd mysql -g mysql -s /sbin/nologin

创建mysql的安装目录和数据存放目录

1
2
mkdir -p /local/server/mysql
mkdir -p /local/webapp/data/mysql

授权、软连接

1
2
chown -R mysql:mysql /local/server/mysql
chown -R mysql:mysql /local/webapp/data/mysql

初始化mysql数据库（会生成root初始化密码，注意保存）

1
/local/server/mysql/bin/mysqld --initialize --user=mysql

※ 临时密码：[Server] A temporary password is generated for root@localhost: G!K+EPYn0=Oe

启动mysql

1
/local/server/mysql/bin/mysqld_safe --user=mysql &

进入数据库修改密码（G!K+EPYn0=Oe是刚才生成的临时密码）并添加mysql远程登录账号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
/local/server/mysql/bin/mysql -uroot -p'G!K+EPYn0=Oe'

mysql> alter user 'root'@'localhost' identified by "password";
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create user root@'%' identified by 'password';
Query OK, 0 rows affected (0.02 sec)
 
mysql> grant all privileges on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

/*查看目前的帐号，有%的root账号表示远程登录账号配置成功*/
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
 
mysql> quit

添加环境变量，并刷新

1
2
echo "PATH=$PATH:/local/server/mysql/bin" >> /etc/profile
source /etc/profile

设置开机自启

1
2
3
4
5
6
cp /local/server/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --list|grep mysqld
chkconfig mysqld on
chkconfig --list|grep mysqld
mysqld          0:关    1:关    2:开    3:开    4:开    5:开    6:关

放行防火墙

1
2
firewall-cmd --zone=public --add-port=3306/tcp
firewall-cmd --zone=public --add-port=3306/tcp --permanent

低版本 Navicat 连接MySQL 8.0

1
2
3
4
5
6
7
8
9
10
11
/*进入MySQL命令行*/
mysql -uroot -p
/*更改加密方式*/
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
/*更改密码*/
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
/*刷新*/
mysql> FLUSH PRIVILEGES;
/*测试连接 Navicat*/

mysql 增删改查

约束条件 约束描述
PRIMARY KEY 主键约束，约束字段的值可唯一地标识对应的记录
NOT NULL 非空约束，约束字段的值不能为空
UNIQUE 唯一约束，约束字段的值是唯一的
CHECK 检查约束，限制某个字段的取值范围
DEFAULT 默认值约束，约束字段的默认值
AUTO_INCREMENT 自动增加约束，约束字段的值自动递增
FOREIGN KEY 外键约束，约束表与表之间的关系 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
show databases；	#查看库
create database 库名；	#创建库
drop database if exists 数据库名；	#删除库（如果存在，删除；不存在，不管）
use 库名；		#进入库

mysql> create table user1(id int(4) primary key auto_increment,
    -> name varchar(20) not null unique,
    -> password varchar(20) not null);		#创建表
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;		#查看表
+---------------+
| Tables_in_zxw |
+---------------+
| user1         |
+---------------+
1 row in set (0.01 sec)

mysql> desc user1;		#查看表结构
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(4)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | NO   | UNI | NULL    |                |
| password | varchar(20) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.53 sec)

mysql> insert into user1(name,password) values('zls','123.com');	#插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from user1;		#查看数据
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | zls  | 123.com  |
+----+------+----------+
1 row in set (0.02 sec)

drop table uesr1;	#删除表

delete from user1 where id=1；	#删除表内容

mysql> update user1 set name='baichi'，password='111.com' where name='dageda';	#更新数据
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user1;		#查看
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  1 | zls    | 123.com  |
|  2 | xiaodi | com.123  |
|  3 | laopo  | 321.com  |
|  4 | baichi | 111.com  |
+----+--------+----------+
4 rows in set (0.00 sec)

扩展

Mariadb主主

yum安装

1
[root@localhost ~]# yum -y install mariadb*

配置（第二台ID变为2）

1
[root@localhost ~]# vim /etc/my.cnf
1
2
3
4
5
server-id = 1		# 从id比主大，且不相同
log-bin=master-bin		# 修改为主
log-slave-update=true	# 允许从机跟随改变而更新内容
relay-log=relay-log-bin		# 指定跟随的方式
relay-log-index=slave-relay-bin.index

重启、关闭防火墙、沙盒

1
2
3
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0

设置密码

1
MariaDB [(none)]> set password=password('123.com');

授权

1
MariaDB [(none)]> grant replication slave on *.* to 'slave'@'192.168.0.%' identified by '123.com';

刷新

1
MariaDB [(none)]> flush privileges;

查看主状态

1
MariaDB [(none)]> show master status;	#（记录文件名称和Position编号）

② 第二台

以从的身份连接主

1
2
3
4
MariaDB [(none)]> change master to master_host='192.168.0.11',master_user='slave',
    -> master_password='123.com',
    -> master_log_file='master-bin.000003',
    -> master_log_pos=612;

开启从状态（stop关闭、reset重置）

1
MariaDB [(none)]> slave start;

查看从状态

1
MariaDB [(none)]> show slave status\G

授权

1
MariaDB [(none)]> grant replication slave on *.* to 'slave1'@'192.168.0.%' identified by '123.com';

刷新

1
MariaDB [(none)]> flush privileges;

查看主状态

1
MariaDB [(none)]> show master status;

① 第一台

以从的身份连接主

1
2
3
4
MariaDB [(none)]> change master to master_host='192.168.0.12',master_user='slave1',
master_password='123.com',
master_log_file='master-bin.000003',
master_log_pos=613;

开启从

1
MariaDB [(none)]> slave start;

查看从状态

1
2
3
4
MariaDB [(none)]> show slave status\G
# Slave_IO_Running: Yes			//这里必须是yes，负责与主机的通信
# Slave_SQL_Running: Yes		//这里必须是yes，负责从机的slave mysql情况
# Last_IO_Errno: 0				//如果是1236，说明主库重新启动；0表示正常

MySQL主主
1
2
3
4
实验环境：
	1. 俩台虚拟机都装有mysql
	2. 虚拟机主IP：192.168.0.11
	3. 虚拟机从IP：192.168.0.12

配置2台文件

1
vim /etc/my.cnf

重启2台mysql

1
systemctl restart mysqld

2台防火墙放行

1
2
firewall-cmd --add-port=3306/tcp
firewall-cmd --add-port=3306/tcp --permanent

2台：登录、设置密码、授权、刷新

1
2
3
4
mysql -uroot -p		#初始密码为空，直接回车
mysql> set password=password('123.com');
mysql> grant replication slave on *.* to 'slave'@'192.168.0.%' identified by '123.com';
mysql> flush privileges;

查看第一台主的状态

1
mysql> show master status;

第二台中的操作：以从的身份连接主、开启从状态、查看从状态

1
2
3
4
5
6
mysql> change master to master_host='192.168.0.11',master_user='slave',
    master_password='123.com',
    master_log_file='master-bin.000001',
    master_log_pos=474;
mysql> slave start;		# stop关闭、reset重置、start开启
mysql> show slave status\G

查看第二台主的状态

1
mysql> show master status;

第一台中的操作：以从的身份连接主、开启从状态、查看从状态

1
2
3
4
5
6
mysql> change master to master_host='192.168.0.12',master_user='slave',
    master_password='123.com',
    master_log_file='master-bin.000001',
    master_log_pos=474;
mysql> slave start;		# stop关闭、reset重置、start开启
mysql> show slave status\G

MySQL主从
1
2
3
4
实验环境：
	1. 俩台虚拟机都装有mysql（yum -y install mariadb*）
	2. 虚拟机主IP：192.168.0.11
	3. 虚拟机从IP：192.168.0.12

配置master

1
vim /etc/my.cnf

配置slave

1
vim /etc/my.cnf

重启2台mysql并放行防火墙

1
2
3
systemctl restart mysqld
firewall-cmd --add-port=3306/tcp
firewall-cmd --add-port=3306/tcp --permanent

登录mysql并设置2台的密码

1
2
mysql -uroot -p
set password=password('123.com');

master上授权（使从能够访问）、刷新、查看master状态（记录文件名称和Position编号）

1
2
3
grant replication slave on *.* to 'slave'@'192.168.0.%' identified by '123.com';
flush privileges;
show master status;

slave上的操作：以从的身份连接主、开启、查看从的状态

1
2
3
4
5
6
mysql> change master to master_host='192.168.0.11',master_user='slave',
    -> master_password='123.com',
    -> master_log_file='master-bin.000001',
    -> master_log_pos=336;
slave start;
show slave status\G



----------- 本文结束 -----------




Buy me a coffee.