第十周练习题

1、在阿里云服务器搭建openv-p-n(有条件的同学再做)

略

2、通过编译、二进制安装MySQL5.7

#配置说明
​
     Linux版本：Centos7
     MySQL版本：mysql-5.7.26
     该文档适用于MySQL版本>=5.7.6
     数据库源码存放目录：/apps/mysql_source #存储空间要大一些
     数据库安装目录：/usr/local/web/mysql
     数据库的数据目录：/home/mysql/data
     
     
#1)安装相关依赖包
[root@localhost ~]#yum install -y cmake gcc gcc-c++ ncurses-devel bison zlib libxml openssl automake autoconf make libtool bison-devel libaio-devel
​
#2)创建用户和数据库目录
[root@localhost ~]#groupadd mysql
[root@localhost ~]#useradd -s /bin/bash -m -g mysql mysql
[root@localhost ~]#mkdir -p  /apps/mysql_source
[root@localhost ~]#mkdir -p /usr/local/web/mysql
[root@localhost ~]#mkdir -p  /home/mysql/data
[root@localhost ~]# chown -R mysql:mysql /home/mysql/data
[root@localhost ~]# chown -R mysql:mysql /usr/local/web/mysql
​
​
#3)下载并解压缩源码包
#下载源码并清除 centos 原有my.cnf配置文件：
[root@localhost ~]#mv /etc/my.cnf /etc/mysql.cof.back
[root@localhost ~]#cd /apps/mysql_source
[root@localhost ~]#wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26.tar.gz
[root@localhost ~]#tar -zxf mysql-5.7.26.tar.gz  -C /apps/mysql_source/
​
#4)安装boost
#mysql5.7.6以后需要安装 boost库文件
[root@localhost ~]#cd /usr/local
[root@localhost ~]#wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz
​
#5)准备编译环境并编译安装
#编译选项：https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html
[root@localhost ~]#cd /apps/mysql_source/mysql-5.7.26/
[root@localhost mysql-5.7.26]#cmake 
-DCMAKE_BUILD_TYPE=RelWithDebInfo 
-DCMAKE_INSTALL_PREFIX=/usr/local/web/mysql 
-DMYSQL_UNIX_ADDR=/usr/local/web/mysql/mysql.sock 
-DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci 
-DMYSQL_DATADIR=/home/mysql/data 
-DSYSCONFDIR=/etc/my.cnf 
-DWITH_MYISAM_STORAGE_ENGINE=1 
-DWITH_INNOBASE_STORAGE_ENGINE=1 
-DWITH_PARTITION_STORAGE_ENGINE=1 
-DENABLE_DEBUG_SYNC=0 
-DENABLED_LOCAL_INFILE=1 
-DENABLED_PROFILING=1 
-DMYSQL_TCP_PORT=3306 
-DWITH_DEBUG=0 
-DWITH_SSL=yes 
-DDOWNLOAD_BOOST=1 
-DWITH_BOOST=/usr/local/boost_1_59_0.tar.gz
#如果编译出现错误，请先删除CMakeCache.txt后，再重新编译
 #cmake参数解释如下：
DCMAKE_INSTALL_PREFIX=/usr/local/mysql:安装路径
DMYSQL_DATADIR=/data/mysql:数据文件存放位置
DSYSCONFDIR=/etc:my.cnf路径
DWITH_MYISAM_STORAGE_ENGINE=1:支持MyIASM引擎
DWITH_INNOBASE_STORAGE_ENGINE=1:支持InnoDB引擎DMYSQL_UNIX_ADDR=/data/mysql/mysqld.sock:连接数据库socket路径 
DMYSQL_TCP_PORT=3306:端口
DENABLED_LOCAL_INFILE=1:允许从本地导入数据
DWITH_PARTITION_STORAGE_ENGINE=1:安装支持数据库分区
DEXTRA_CHARSETS=all:安装所有的字符集
DDEFAULT_CHARSET=utf8:默认字符
DWITH_EMBEDDED_SERVER=1:嵌入式服务器
​
#该命令中可以通过添加-j参数指定多线程工作，如make -j2 && make install -j2 则使用2个CPU核进行make
[root@localhost ~]#make -j2 && make install -j2
​
#6）设置开机自启动
[root@localhost ~]#cp /usr/local/web/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]#chmod +x /etc/init.d/mysqld
[root@localhost ~]#chkconfig --add mysqld
​
#7）准备my.cnf文件
[root@localhost ~]#vim /etc/my.cnf
[mysqld]
basedir = /usr/local/web/mysql
datadir = /home/mysql/data
innodb_buffer_pool_size = 128M
explicit_defaults_for_timestamp = true
socket = /usr/local/web/mysql/mysql.sock
[mysqld_safe]
log-error = /home/mysql/data/error.log
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
​
​
#8）配置环境变量，使/usr/local/web/mysql/bin，lib目录下所有命令可以在任意目录执行。
[root@localhost bin]# echo 'PATH=$PATH:/usr/local/web/mysql/bin:/usr/local/web/mysql/lib/' >> /etc/profile
[root@localhost bin]# . /etc/profile
​
​
#9）初始化数据库
#如果出现如下信息：unknown variable 'defaults-file=/data/mysql/my.cnf'，请保证--defaults-file配置选项在最前面
[root@localhost data]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/web/mysql --datadir=/home/mysql/data  --user=mysql
2021-11-03T05:42:04.327406Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-11-03T05:42:04.355994Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-11-03T05:42:04.413061Z 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: c660a456-3c68-11ec-8cf0-000c29b37a91.
2021-11-03T05:42:04.414512Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-11-03T05:42:04.533331Z 0 [Warning] CA certificate ca.pem is self signed.
2021-11-03T05:42:04.632590Z 1 [Note] A temporary password is generated for root@localhost: .Y)uL3E=riO5             #生成的随机密码
#初始化报错参考文档：http://blog.csdn.net/nicolelili1/article/details/78580681
​
#10）启动数据库
#mysqld_safe --defaults-file=/etc/my.cnf & 或者 service mysqld start
[root@localhost data]# service mysqld start
Starting MySQL.Logging to '/home/mysql/data/error.log'.
                                                          [  OK  ]
#mysql启动报错：Starting MySQL... ERROR! The server quit without updating PID file参考：http://blog.51cto.com/732233048/1636409，启动不成功，修改my.cnf后需要重新初始化
​
#11）登录并修改密码
#以.Y)uL3E=riO5这个初始化密码登录                                                     
[root@localhost data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.26
​
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
​
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
​
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
#设置新密码
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
#刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
​
#以新密码登录数据库
[root@localhost data]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 6
Server version: 5.7.26 Source distribution
​
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
​
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
​
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
#创建用户  root'@'%   表示root在所有主机上登录数据库
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
​
#授权root在所有主机上登录数据库，有所有数据库的权限
mysql> GRANT all ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
​
#用centos8机器，yum安装mariadb数据库软件，验证远程机器登录数据库
[root@centos8 ~]#mysql -h10.0.0.152 -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.7.26 Source distribution
​
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
​
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
​
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.001 sec)
​
#关闭数据库：mysqladmin shutdown -uroot -S /usr/local/web/mysql/mysql.sock -p 或 service mysqld stop
​
#MySQL5.7 和以前版本不同点：　　　　
​
1.初始化工具不同
　　　MySQL5.6.xx使用的是mysql_install_db，MySQL5.7.6+官方推荐使用mysqld –initialize。
2.初始化数据库不同
　　　MySQL5.6.xx初始化之后存在mysql,information_schema,performance_schema,test四个数据库，MySQL5.7.6+初始化之后存在mysql,information_schema,performance_schema,sys四个数据库。
3.初始化用户不同
　　　　MySQL5.6.xx初始化之后存在root@localhost,root@'::1',root@'hostname',''@'localhost',''@'hostname'五个用户，MySQL5.7.6+初始化之后存在mysql.sys,root@localhost，用户
4.初始化root密码
　　　MySQL5.6.xx初始化之后root用户密码为空，MySQL5.7.6+初始化之后会为root@localhost用户生成随机密码。
​
#MySQL安装常用参考文档：
安装过程：
1. http://blog.csdn.net/sungsasong/article/details/76278361
2.https://segmentfault.com/a/1190000012099346
3.http://www.centoscn.com/mysql/2016/0315/6844.html
4.https://www.cnblogs.com/liqing1009/p/7879945.html
make && makeinstall 时报错参考：
http://www.yunweijilu.com/index.php/home/index/article/aid/135
centos 7 防火墙设置参考 ：https://jingyan.baidu.com/article/adc81513944addf723bf73af.html
MySQL5.7初始密码查看及重置:http://blog.csdn.net/t1anyuan/article/details/51858911
源码安装后卸载再装：https://www.jianshu.com/p/e54ff5283f18
MySQL常见错误解决方法http://www.360doc.com/content/15/0201/13/3200886_445438524.shtml
MYSQL MY.CNF 参数优化参考：http://www.cnblogs.com/yangchunlong/p/8478275.html

3、二进制安装mariadb10.4

#下载二进制安装包（centos7系统）：
[root@localhost ~]#wget http://mirrors.dotsrc.org/mariadb/mariadb-10.4.21/bintar-linux-x86_64/mariadb-10.4.21-linux-x86_64.tar.gz
#解压到安装目录/usr/local/mariadb
[root@localhost ~]# mkdir /usr/local/mariadb
[root@localhost ~]# tar zxf mariadb-10.4.21-linux-x86_64.tar.gz  -C /usr/local/mariadb
[root@localhost ~]# ls /usr/local/mariadb/mariadb-10.4.21-linux-x86_64/
bin      CREDITS  include         lib  mysql-test  README-wsrep  share      support-files
COPYING  docs     INSTALL-BINARY  man  README.md   scripts       sql-bench  THIRDPARTY
#[root@localhost mariadb]# mv /usr/local/mariadb/mariadb-10.4.21-linux-x86_64/* /usr/local/mariadb/
​
​
#创建用户及组
[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -s /sbin/nologin -r -g mysql mysql
​
#配置服务开机自启动
[root@localhost ~]# cp /usr/local/mariadb/mariadb-10.4.21-linux-x86_64/support-files/mysql.server  /etc/init.d/mysqld
[root@localhost ~]# chmod +x /etc/init.d/mysqld
[root@localhost ~]# chkconfig --add mysqld
​
#新建数据目录与权限
[root@localhost ~]# mkdir -p /data/mariadb
[root@localhost ~]# chown -R mysql.mysql /data/mariadb
[root@localhost ~]# chown -R mysql.mysql /usr/local/mariadb
​
#准备配置文件my.cnf
[root@localhost ~]# vim /usr/local/mariadb/my.cnf
[mysqld]
#basedir=/usr/local/mariadb/ #二进制安装pass the --basedir option
datadir=/data/mariadb/
port=3306
pid-file=/data/mariadb/mysql.pid
socket=/tmp/mysql.sock
​
[mysqld_safe]
log-error=/data/mariadb/mysql.log
​
[client]
port=3306
socket=/tmp/mysql.sock
default-character-set=utf8
​
​
#数据库初始化
[root@localhost ~]# /usr/local/mariadb/scripts/mysql_install_db  --defaults-file=/usr/local/mariadb/my.cnf --user=mysql --basedir=/usr/local/mariadb/ --datadir=/data/mariadb/
[root@localhost ~]# echo $?
0
​
#配置环境变量
[root@localhost ~]# echo "PATH=$PATH:/usr/local/mariadb/bin" > /etc/profile.d/mairadb.sh[root@localhost ~]# chmod +x /etc/profile.d/mairadb.sh
[root@localhost ~]# . /etc/profile.d/mairadb.sh
​
#安全模式启动设置root密码
[root@localhost data]# mysqld_safe --defaults-file=/usr/local/mariadb/my.cnf &
​
[root@localhost data]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 8
Server version: 10.4.21-MariaDB MariaDB Server
​
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
​
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
​
MariaDB [(none)]> set password=password('123456');
Query OK, 0 rows affected (0.001 sec)
​
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
​
#安全初始化
[root@localhost data]# mysql_secure_installation --basedir=/usr/local/mariadb
print: /usr/local/mariadb/bin/my_print_defaults
​
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
​
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
​
Enter current password for root (enter for none):
OK, successfully used password, moving on...
​
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
​
You already have your root account protected, so you can safely answer 'n'.
​
Switch to unix_socket authentication [Y/n] y
Enabled successfully!
Reloading privilege tables..
 ... Success!
​
​
You already have your root account protected, so you can safely answer 'n'.
​
Change the root password? [Y/n] n
 ... skipping.
​
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
​
Remove anonymous users? [Y/n] y
 ... Success!
​
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
​
Disallow root login remotely? [Y/n] n
 ... skipping.
​
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
​
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
​
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
​
Reload privilege tables now? [Y/n] y
 ... Success!
​
Cleaning up...
​
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
​
Thanks for using MariaDB!
​
#重启服务后，再次使用root用户，密码123456登录成功
[root@localhost data]# systemctl restart mysqld
[root@localhost data]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 16
Server version: 10.4.21-MariaDB MariaDB Server
​
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
​
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
​
MariaDB [(none)]>
​