安装MySQL的两种常见方法

MariaDB 的相关知识

MySQL 是一种最常见的关系型数据库，在经典的 LAMP 架构中代表 M。在被甲骨文公司收购之后，MySQL 的作者重新起了一个开源项目，叫 MariaDB。所以在很多新的 Linux 发行版系统中，可以通过安装 MariaDB 来使用这个数据库。

安装 MariaDB 的三种方式：

• 官方提供的二进制包
• 官方提供的 yum 软件源
• cmake 编译安装

系统默认镜像已经提供 MariaDB 的安装，为什么还需要安装 MariaDB？

1. 因为系统发行版由于更注重于软件的稳定，提供的 MariaDB 版本还在 5.5。注：2016 年
2. 由于 MariaDB 在 5.5 以后已经有许多性能上的优化提升，生产环境需要更高版本的 MariaDB。
3. 因为 cmake 的方式与传统的编译安装并不一样且繁琐，所以一般前两种方法较为常用。
除了 MariaDB，其实 Tomcat 也是这种情况：大家需要新版本，但是编译很麻烦。
官方就为大家提供他们编译并打包好好的二进制程序给大家下载。‘

配置官方 yum 源，并安装 MariaDB

(1)首先我们需要在官方网站寻找并配置一个与系统合适的镜像。

本文使用的系统版本为 CentOS7.1
注意：系统已经关闭了 selinux，以及 iptables。
因为我们此处要使用 yum 源安装 MariaDB，所以我们选用下面的这种方式。
而且现在 MariaDB 在已经有清华镜像，所以我们不用担心官方镜像安装特别慢的问题。

(2)找到合适的镜像之后新建一个文件，如 /etc/yum.repos.d/mariadb.repo
配置它为 yum 源，内容如下：

# MariaDB 10.1 CentOS repository list
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDBbaseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.1.19/yum/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

通过命令查看 MariaDB-server 的信息，我们可以看到 update 镜像提供 5.5.50 的版本。
而我们刚才配置的 mariadb 镜像则是 10.1.19
注意：在开始的时候也说过，MySQL 被甲骨文收购了。
因为 MariaDB 说 MySQL 有的功能他们都有，但是为了不让人误会，所以跳出了 5 系列的循环。
也就是 MySQL5.6 和 5.7 其实对应了 MariaDB 的 10.0 和 10.1。并不是已经跨越了 5 个版本。

[root@bc ~]# yum list MariaDB-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.163.com
 * epel: ftp.cuhk.edu.hk
 * extras: mirrors.tuna.tsinghua.edu.cn
 * updates: mirrors.163.com
Available Packages
MariaDB-server.x86_64                    10.1.19-1.el7.centos                    mariadb
mariadb-server.x86_64                    1:5.5.50-1.el7_2                        update

(3 开始安装 MariaDB 吧！

[root@bc ~]# yum install MariaDB-server.x86_64 MariaDB-client.x86_64 -y

安装的这两个组件，MariaDB-server 提供数据存储功能，MariaDB-client 提供数据库的链接功能。
yum 安装软件的好处在于便捷，但是经常由于没有人维护和更新最新版的 rpm 包，所以有的时候，我们也只能另辟蹊径。

(4)查看已经安装的 MariaDB 的版本

[root@bc ~]# mysql --version
mysql  Ver 15.1 Distrib 10.1.19-MariaDB, for Linux (x86_64) using readline 5.1

已经成功安装 10.1.19 的 MariaDB 了。

使用官方编译好的二进制程序安装 MariaDB

(1)首先我们依然是在官方网站寻找一个稳定的版本。STABLE

官方会提示，请不要在生产环境使用 beta 和 alpha 版本的数据库。
他们的主要功能一般是用来测试和排错，所以不稳定。
这里我们选择安装一个 10.0 系列的 Stable 的数据库。
我们打算在安装之后，连接刚才 10.1 的数据库并插入语句。
(2)选择二进制格式的安装方式

根据 CPU 和系统, 我们选择安装这个包。
官方编译好的二进制包都是 tar.gz 格式的，不过注意不要安装到源码包。

(3)解压缩文件并创建软连接。

[root@node2 ~]# tar -xf mariadb.tar.gz -C /usr/local/
[root@node2 ~]# cd /usr/local/
[root@node2 local]# ln -sv mariadb-10.0.28-linux-x86_64/ mysql‘mysql’->‘mariadb-10.0.28-linux-x86_64/’

为什么要做软连接呢？
一是因为以后我们以后安装其他版本的时候方便一点点。
二是名字够简短，容易辨认。
(4)把 mysql 的执行文件路径导出到工作路径

[root@node2 mysql]# vim /etc/profile.d/mariadb.sh
    export PATH=/usr/local/mysql/bin:$PATH
[root@node2 mysql]# chmod +x !$
[root@node2 ~]# . /etc/profile.d/mariadb.sh

(5)创建用户，修改文件权限，并创建数据存放目录

[root@node2 mysql]# useradd -r mysql
[root@node2 mysql]# id mysql
  uid=996(mysql) gid=994(mysql) groups=994(mysql)
[root@node2 mysql]# chown mysql.mysql /usr/local/mysql/* -R
[root@node2 mysql]# mkdir /datadir
[root@node2 mysql]# chown mysql.mysql -R /datadir/

创建用户和数据存放目录是为了一会儿初始化的时候准备。
而且 mysql 有一个常见问题就是，权限不对就启动不成功，所以要注意。

(6)初始化数据库

[root@node2 ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/datadir --basedir=/usr/local/mysql
Installing MariaDB/MySQL system tables in '/datadir' ...
161210  8:43:23 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
161210  8:43:23 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.28-MariaDB) starting as process 3139 ...
161210  8:43:23 [Note] InnoDB: Using mutexes to ref count buffer pool pages
161210  8:43:23 [Note] InnoDB: The InnoDB memory heap is disabled
161210  8:43:23 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
161210  8:43:23 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
161210  8:43:23 [Note] InnoDB: Compressed tables use zlib 1.2.3
161210  8:43:23 [Note] InnoDB: Using Linux native AIO
161210  8:43:23 [Note] InnoDB: Using CPU crc32 instructions
161210  8:43:23 [Note] InnoDB: Initializing buffer pool, size = 128.0M
161210  8:43:23 [Note] InnoDB: Completed initialization of buffer pool
161210  8:43:23 [Note] InnoDB: Highest supported file format is Barracuda.
161210  8:43:23 [Note] InnoDB: Log scan progressed past the checkpoint lsn 49463
161210  8:43:23 [Note] InnoDB: Database was not shutdown normally!
161210  8:43:23 [Note] InnoDB: Starting crash recovery.
161210  8:43:23 [Note] InnoDB: Reading tablespace information from the .ibd files...
161210  8:43:23 [Note] InnoDB: Restoring possible half-written data pages 
161210  8:43:23 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1606610
161210  8:43:23 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
161210  8:43:24 [Note] InnoDB: 128 rollback segment(s) are active.
161210  8:43:24 [Note] InnoDB: Waiting for purge to start
161210  8:43:24 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.32-79.0 started; log sequence number 1606610
161210  8:43:24 [Note] Recovering after a crash using mysql-bin
161210  8:43:24 [Note] Starting crash recovery...
161210  8:43:24 [Note] Crash recovery finished.
161210  8:43:24 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
161210  8:43:24 [Note] InnoDB: FTS optimize thread exiting.
161210  8:43:24 [Note] InnoDB: Starting shutdown...
161210  8:43:25 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
161210  8:43:27 [Note] InnoDB: Shutdown completed; log sequence number 1622831
OK
Filling help tables...
161210  8:43:27 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
161210  8:43:27 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.28-MariaDB) starting as process 3169 ...
161210  8:43:27 [Note] InnoDB: Using mutexes to ref count buffer pool pages
161210  8:43:27 [Note] InnoDB: The InnoDB memory heap is disabled
161210  8:43:27 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
161210  8:43:27 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
161210  8:43:27 [Note] InnoDB: Compressed tables use zlib 1.2.3
161210  8:43:27 [Note] InnoDB: Using Linux native AIO
161210  8:43:27 [Note] InnoDB: Using CPU crc32 instructions
161210  8:43:27 [Note] InnoDB: Initializing buffer pool, size = 128.0M
161210  8:43:27 [Note] InnoDB: Completed initialization of buffer pool
161210  8:43:27 [Note] InnoDB: Highest supported file format is Barracuda.
161210  8:43:27 [Note] InnoDB: 128 rollback segment(s) are active.
161210  8:43:27 [Note] InnoDB: Waiting for purge to start
161210  8:43:27 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.32-79.0 started; log sequence number 1622831
161210  8:43:27 [Note] InnoDB: FTS optimize thread exiting.
161210  8:43:27 [Note] InnoDB: Starting shutdown...
161210  8:43:28 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
161210  8:43:30 [Note] InnoDB: Shutdown completed; log sequence number 1622841
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/local/mysql/bin/mysqladmin' -u root password 'new-password'
'/usr/local/mysql/bin/mysqladmin' -u root -h node2.bc.com password 'new-password'

Alternatively you can run:
'/usr/local/mysql/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/datadir'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Support MariaDB development by buying support/new features from MariaDB
Corporation Ab. You can contact us about this at sales@mariadb.com.
Alternatively consider joining our community based development effort:
http://mariadb.com/kb/en/contributing-to-the-mariadb-project/

其实都是一些输出信息。但是这里仔细看的话，他们有教我们下一步怎么做。

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/local/mysql/bin/mysqladmin' -u root password 'new-password'
'/usr/local/mysql/bin/mysqladmin' -u root -h node2.bc.com password 'new-password'

Alternatively you can run:
'/usr/local/mysql/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/datadir'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl

(7)设置开机启动

[root@node2 mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@node2 mysql]# chkconfig --add mysqld
[root@node2 ~]# chkconfig mysqld on

(8)修改配置文件，加入几个常用参数。

[root@node2 mysql]# cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf
[root@node2 ~]# vim /etc/my.cnf
  [mysqld]
  datadir=/datadir
  user=mysql
  skip_name_resolve
  innodb_file_per_table

(9)启动数据库

[root@node2 ~]# service mysqld start
Starting MySQL.161210 08:44:14 mysqld_safe Logging to '/datadir/node2.bc.com.err'.
 SUCCESS!

启动成功！
注意的是，先启动 MySQL 是无法进行下一步安全设置的。
因为你想想不开服务，怎么改密码和清除示例。

(10)初始化安全设置

[root@node2 ~]# mysql_secure_installation 

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
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] Y  
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


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!

(11)进入数据库！

[root@node2 ~]# mysql -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.0.28-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)