1. MySQL数据库介绍

1.1 MySQL介绍

MySQL属于传统关系型数据库产品，它开放式的架构使得用户选择性很强，同时社区开发与维护人数众多。其功能稳定，性能卓越，且在遵守GPL协议的前提下，可以免费使用与修改，也为MySQL的推广与使用带来了更多的利好。在MySQL成长与发展过程中，支持的功能逐渐增多，性能也不断提高，对平台的支持也越来越多。

MySQL是一种关系型数据库管理系统，关系型数据库的特点是将数据保存在不同的表中，再将这些表放入不同的数据库中，而不是将所有数据统一放在一个大仓库里，这样的设计增加了MySQL的读取速度，而且灵活性和可管理性也得到了很大提高。访问及管理MySQL数据库的最常用标准化语言为SQL结构化查询语言。

1.2 MariaDB数据库的诞生背景介绍

自甲骨文公司收购MySQL后，其在商业数据库与开源数据库领域市场的占有份额都跃居第一，这样的格局引起了业内很多的人士的担忧，因为商业数据库的老大有可能将MySQL闭源。为了避免Oracle将MySQL闭源，而无开源的类MySQL数据库可用，MySQL社区采用分支的方式来避开这个风险。MariaDB数据库就这样诞生了，MariaDB是一个向后兼容，可能在以后替代MySQL的数据库产品，其官方地址为https://mariadb.org/。不过，这里还是建议大家选择更稳定、使用更广泛的MySQL数据库，可以先测试MariaDB数据库，等使用的人员更多一些，社区更活跃后再考虑使用为好。

 

1.3 什么是MySQL多实例

       简单地说，MySQL多实例就是在一台服务器上同时开启多个不同的服务器端口（如：3306、3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务器端口来提供服务。

这些MySQL多实例共用一套MySQL安装程序，使用不同的my.cnf（也可以相同）配置文件、启动程序（也可以相同）和数据文件。在提供服务时，多实例MySQL在逻辑上看起来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。

打个比方吧，MySQL多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（CPU、Mem、Disk）、软件资源（Centos操作系统）可以看作房子的卫生间、厨房、客厅，是房子的公用资源。若你是北漂的小伙伴，与朋友一起租房，相信对此能更好地理解。大家蜗居在一起，在自己的卧室休息，出来活动时肯定是要共用上述公共资源的。这样该明白MySQL多实例了吧。

其实很多网络服务都是可以配置多实例的，例如Nginx、Apache、Haproxy、Redis、Memcahe等。这在门户网站使用得很广泛。

 

1.4 MySQL多实例的生产应用场景

1.资金紧张型公司的选择

若公司资金紧张，公司业务访问量不太大，但又希望不同业务的数据库服务各自尽量独立地提供服务而互相不受影响，同时，还需要主从复制等技术提供备份或读写分离服务，那么，多实例就再好不过了。例如：可以通过3台服务器部署9~15个实例，交叉做主从复制、数据备份及读写分离，这样就可达到9~15台服务器每个只装一个数据库才有的效果。这里要强调的是，所谓的尽量独立是相对的。

 

2.并发访问不是特别大的业务

当公司业务访问量不太大的时候，服务器的资源基本上都浪费了，这时就很适合多实例的应用，如果对SQL语句的优化做得比较好，MySQL多实例会是一个很值得使用的技术，即使并发很大，合理分配好系统资源，搭配好服务，也不会有太大问题。

 

3.门户网站应用MySQL多实例场景

门户网站通常都会使用多实例，因为配置硬件好的服务器，可节省IDC机柜空间，同时，跑多实例也会减少硬件资源跑不满的浪费。比如，百度公司的很多数据库都是多实例，不过，一般是从库多实例，例如某部门中使用的IBM服务器为48核CPU，内存96GB，一台服务器跑3~4个实例；此外，新浪网使用的也是多实例，内存48GB左右。

说明：据老男孩调查，新浪网的数据库单机1~4个数据库实例的居多，其中又数1~2个的最多，因为大业务占用的机器比较多。服务器是DELL R510的居多，CPU是E5210，48GB内存，磁盘12×300G SAS，做RAID10，此为门户网站的服务器配置参考，希望能给读者购买服务器及部署实例一点数据帮助。

另外，新浪网站安装数据库时，一般采用编译安装的方式，并且做成rpm包，以便统一使用。

 

2. MySQL数据库源码安装

 

安装版本：mysql-5.5.49

 

2.1 MySQL数据库源码编译安装

  

 

1. 安装相关包cmake软件和ncurses包

yum -y install cmake
rpm -qa cmake
yum -y install ncurses-devel 

 

2. 创建MySQL数据库管理用户mysql

useradd mysql -M -s /sbin/nologin 

  创建软件存放目录

mkdir -p /home/oldboy/tools 
cd /home/oldboy/tools

   

3. 上传mysql软件包解压并编译

rz mysql-5.5.49.tar.gz 
tar xf mysql-5.5.49.tar.gz 
cd mysql-5.5.49 
#---------------------------以下是编译参数-----------------
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.49 \
-DMYSQL_DATADIR=/application/mysql-5.5.49/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.49/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0
#----------------------------------------------------------
make && make install 

 

4. 为mysql创建一个lilk（软连接），工作常用

ln -s /application/mysql-5.5.49/ /application/mysql 
ll /application/ 

  

5. 授权mysql用户管理数据库

cd /application/mysql 
chown -R mysql.mysql /application/mysql*

 

6. 初始化数据库

./scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql  
## --basedir是指定安装的根；--datadir指定数据文件；--user指定管理用户；这会在—datadir下的目录生成一堆管理文件，回车后看到两个OK就是对的 
## 故障点：1. /tmp 目录权限必须是1777（默认）；2. 主机名要解析，不然会报故障

 

7. 启动数据库

cd /application/mysql 
\cp support-files/my-small.cnf /etc/my.cnf 
sed -i  's#/usr/local/#/application/#g' /etc/my.cnf 
/application/mysql/bin/mysqld_safe --user=mysql &     #<== 启动数据库命令
lsof -i :3306 
# 执行以上四步，先使用cp将mysql配置文件放到/etc下面，然后在启动检查即可。
echo "/application/mysql/bin/mysqld_safe --user=mysql &" >>/etc/rc.local  #<== 加入开机自启动

 

8. 将mysql命令加入PATS变量

PATH="/application/mysql/bin:PATH" 
echoPATH 
echo 'PATH="/application/mysql/bin:$PATH" ' >>/etc/profile 
tail 6 /etc/profile 
source /etc/profile 
# 如果不配置mysql的全局路径，则无法使用直接敲mysql等命令管理数据库，而只能采用/application/mysql/bin/mysql）全路径执行才行，将重新定义的PATH第一条命令加入到/etc/profile里面，永久生效 （新加变量要放到前面）

 

9. 简化mysql启动命令

cd /application/mysql/support-files 
sed -i 's#/usr/local/#/application/#g' mysql.server 
  # mysql.server脚本里面也有/usr/local/路径，所以要进行替换
cp mysql.server /etc/init.d/mysqld  
chmod +x /etc/init.d/mysqld 
  # 将脚本mysql.server脚本拷贝到/etc/init.d里面进行管理并授权 
/etc/init.d/mysqld stop
/etc/init.d/mysqld start
  # 现在可以正常启停
chkconfig --add mysqld
chkconfig --list mysqld
  # 将mysqld服务添加到chkconfig管理，并设置成开机自启动（加入后默认是开，不过还是得查看确认一下）

 

----------------现在就可以直接输入命令进入数据库啦！！！-----------------

[root@nfs01 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.49 Source distribution
Copyright (c) 2000, 2016, 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>          --------成功！！！！

 

2.2 MySQL多实例安装

 

环境：接着本章节2.1环境安装多实例

说明：因为mysql数据的默认端口为3306，现在我们要增加多实例，所以端口和路径也要相应的做下改变，方便管理，安装两个多实例：端口为3307和3308；配置文件的路径为：/data/{3307,3308}

 

1. 创建多实例数据库目录并授权

mkdir -p /data/{3307,3308}/data/ 
chown -R mysql.mysql /data/

 

2. 在根下上传多实例的配置文件

cd /
rz data.tar.gz（这是3307和3308多实例）下面使用的是这个
tar xf data.tar.gz     
#<== 此压缩包解压后会在/data/{3307,3308}目录下生成my.cnf（配置文件），mysql（启动命令），以及一些日志文件等！
chown -R mysql.mysql /data/330*   #<== 对解压后生成的配置文件授权

 

3. 初始化多实例数据库

cd /application/mysql/scripts/ 
./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307/data/ --user=mysql 
  #<== 初始化端口为3307的数据库目录
./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308/data/ --user=mysql 
  #<== 初始化端口为3308的数据库目录

   授权两个多实例3307和3308启动命令授权

[root@nfs01 data]# chown -R mysql.mysql /data   
[root@nfs01 data]# find /data -type f -name "mysql"
/data/3307/mysql
/data/3308/mysql
[root@nfs01 data]# find /data -type f -name "mysql"|xargs chmod +x 
[root@nfs01 data]# ls *
3307:
my.cnf  mysql

3308:
my.cnf  mysql

  

4. 启动多实例数据库

data/3307/mysql start 
data/3308/mysql start 
[root@nfs01 3307]# ss -lntup|grep 330                                
tcp    LISTEN   0   50     *:3306      *:*      users:(("mysqld",29124,10))
tcp    LISTEN   0   600    *:3307      *:*      users:(("mysqld",28194,11))
tcp    LISTEN   0   600    *:3308      *:*      users:(("mysqld",22822,12)) 
echo "data/3307/mysql start" >>/etc/rc.local  #<== 将端口为3307的多实例启动命令加入开机自启
echo "data/3308/mysql start" >>/etc/rc.local  #<== 将端口为3308的多实例启动命令加入开机自启
tail -2 /etc/rc.local       #<== 检查确认是否加入成功 

  

5. 登录多实例mysql数据库（以下是mysql命令加入环境变量后的）

[root@db01 /]# mysql -uroor -p -S /data/3307/mysql.sock    #<== 多实例3307数据库登录命令
Enter password:           #<== 当前没有设置密码，直接回车即可登录

[root@db01 /]# mysql -uroor -p -S /data/3308/mysql.sock    #<== 多实例3308数据库登录命令
Enter password:           #<== 当前没有设置密码，直接回车即可登录

6. 因为脚本里面设置的密码是oldboy 所以如果我们不设置将无法停止MySQL

mysqladmin password oldboy -S /data/3307/mysql.sock 
mysqladmin password oldboy -S /data/3308/mysql.sock
mysql -uroot -poldboy -S /data/3307/mysql.sock    #<== 有密码登录数据库3307
mysql -uroot -poldboy -S /data/3308/mysql.sock    #<== 有密码登录数据库3308

 

2.3 手动增加一个多实例3309

  

1. 创建一个3309多实例的目录

[root@db01 /]# mkdir -p /data/3309/data/

  

2. 复制一份实例到指定的目录

[root@db01 /]# \cp /data/3306/{my.cnf,mysql} /data/3309 
[root@db01 /]# ll /data/3309
total 12
drwxr-xr-x 2 root root 4096 Aug 25 01:04 data 	 #<== 3309多实例数据文件
-rw-r--r-- 1 root root 1899 Aug 25 01:04 my.cnf 	 #<== 3309多实例配置文件
-rwxr-xr-x 1 root root 1307 Aug 25 01:04 mysq	 #<== 3309多实例启动命令

 

3. 修改cp过来的配置文件及启动脚本

[root@db01 /]# \cp /data/3309/{mysql,mysql.bak}   	#<== 修改前备份    
[root@db01 /]# \cp /data/3309/{my.cnf,my.cnf.bak}  		#<== 修改前备份
ll /data/3309/
----------------------以下使用sed命令批量替换，主要修改两处地方，如下--------------------
[root@db01 /]# sed -i 's#3306#3309#g' /data/3309/{my.cnf,mysql}  #<== 修改端口及文件路径
[root@db01 /]# sed -i 's#server-id = 1#server-id = 8#g' /data/3309/my.cnf  #<== 修改多实例id

   

4. 设置3309数据库实例所有者为mysql

[root@db01 /]# chown -R mysql.mysql /data/3309/ 
[root@db01 /]# chmod 700 /data/3309/mysql
[root@db01 /]# ll /data/3309/

  

5. 对新增加的3309多实例进行初始化数据库操作

[root@db01 scripts]# cd /application/mysql/scripts/
[root@db01 scripts]# ./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3309/data/ --user=mysql 

 

6. 对新增加的实例（3309）启动命令授执行权限

[root@db01 scripts]# chmod +x /data/3309/mysql

  

7. 启动新增多实例3309数据库并检查

[root@db01 scripts]# /data/3309/mysql start   #<== 启动多实例3309数据库
MySQL is running...
[root@db01 scripts]# ss -lntup|grep 3309      #<== 检查是否启动成功
tcp    LISTEN     0      600      *:3309        *:*      users:(("mysqld",16224,1

 

8. 登录3309数据库

[root@db01 scripts]# mysql -S /data/3309/mysql.sock 
mysql>          #<== 登录成功

  

9. 将数据库命令加入开机自启动（/etc/rc.local）

[root@db01 scripts]# echo "/data/3309/mysql start" >>/etc/rc.local 
[root@db01 scripts]# tail -1 /etc/rc.local 

 

10. 因为脚本里面设置的密码是oldboy 所以如果我们不设置将无法停止MySQL

mysqladmin password oldboy -S /data/3309/mysql.sock
mysql -uroot -poldboy -S /data/3309/mysql.sock    #<== 有密码登录数据库

------------------现在不光能启动了，也可以关闭数据库了！！！-----------------

 

2.4 多实例的配置文件和启动脚本命令

 

说明：添加多实例时，只需要复制my.cnf配置文件和mysql启动命令即可，只需要更改下两个文件里面的内容，添加多实例更改里面内容时，建议使用sed命令更改。

 

1. 多实例配置文件对比，也是我们添加多实例需要更改注意的地方

以下以多实例3307和3308多实例配置文件做对比

 

2. 多实例启动脚本/data/330*/mysql对比

说明：该启动脚本中不一样的内容只有一项，就是port不同，其他的都一样。

  

3. MySQL报错问题及解决

 

实例1：MySQL多实例启动故障排错说明

如果MySQL多实例有服务器没有启动，排查办法如下：

       > 如果发现没有显示MySQL对应案例的端口，请稍后等待几秒在检查，MySQL服务的启动比web服务等会慢一些。

     > 如果还不行，请查看MySQL服务对应实例的错误日志，错误日志路径在my.cnf配置的最下面定义，例如：3306实例的错误日志为

[root@db01 /]# grep log-error /data/3306/my.cnf|tail -1
log-error=/data/3306/mysql_oldboy3306.err

   > 细看多有执行命令返回的屏幕输出，不要忽略关键的输出内容。

      > 辅助查看系统日志/var/log/messages

     > 如果是MySQL关联了其他服务，要同时查看相关服务的日志。

     > 仔细阅读，重新查看操作的步骤是否正确，书写的命令及字符是不是都对。

 

实例2：/tmp目录权限问题

给出提示“ERROR：1004 Can’t creale file ‘/tmp/#sq1300e_1_0.frm(errno:13)”

在执行初始化数据库命令是可能就会遇到这样的错误，错误提示如下：

问题原因：/tmp目录不能创建文件，所以解决办法为给/tmp目录增加权限。如下：

[root@db01 /]# ll -d /tmp/    
drwxr-xr-x. 4 root root 4096 Aug 24 23:18 /tmp/ 
[root@db01 /]# chmod 1777 /tmp/       #<== 为/tmp目录授权为1777权限即可
[root@db01 /]# ll -d /tmp/     
drwxrwxrwt. 4 root root 4096 Aug 24 23:18 /tmp/

 

关于MySQL初始化，报以下错误提示！

[root@bd01 scripts]# ./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3306/data/ --user=mysql
Installing MySQL system tables...
160824 11:38:37 [Note] /application/mysql//bin/mysqld (mysqld 5.5.49) starting as process 36154 ...
ERROR: 3  Error writing file './mysql/db.frm' (Errcode: 28)        #<== 出现错误的地方
160824 11:38:37 [ERROR] Aborting

问题原因：最后查出原因，原来是磁盘空间不足所导致MySQL无法写入数据！从而无法初始化成功数据库

[root@bd01 scripts]# df -h
Filesystem  	  Size    Used    Avail    Use%    Mounted on
/dev/sda3 	  3.7G    3.6G     0      100%      /
tmpfs  		  238M     0    238M     0%      /dev/shm
/dev/sda1	  190M   36M    145M    20%     /boot

    

4. MySQL数据库基础安全

  

1. 启动程序设置700，属主和用户组为mysql。

2. 为MySQL超级用户root设置密码。

3. 如果要求严格可以删除root用户，创建其它管理用户，例如admin。

4. 登录时尽量不要在命令行爆漏密码，备份脚本中如果有密码，给设置700，属主和用户组为mysql或root。

5. 删除默认存在的test库。

6. 初始删除无用的用户，只保留

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

    | root | 127.0.0.1 |

    | root | localhost |

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

7. 不要一个用户管所有的库，尽量专库专户。

8. 授权用户对应的主机不要用%，权限不要给all，最小化授权。从库只给select。

9. 清理mysql操作日志文件~/.mysql_history（600权限，可以不删）。

10. 禁止开发获取到web连接的密码，禁止开发连接操作生产对外的库。

11. MySQL服务器禁止使用外网IP。

12. 防SQL注入（web），php.init或web开发插件空间，waf控制。

mysql安全加固：http://www.cnblogs.com/yuwensong/archive/2013/03/26/2981965.html

  

5. 配置及管理MySQL数据库命令

  

1. 配置MySQL单实例及多实例数据库启动命令

   服务的开机自启动很关键，MySQL多实例的自启动也不例外，把MySQL多实例的启动命令介入/etc/rc.local，实现开机自启动。

单实例

/application/mysql/bin/mysqld_safe --user=mysql &     #<== 单实例启动命令 

多实例

data/3307/mysql start    #<== 端口为3307的多实例启动
data/3306/mysql start    #<== 端口为3306的多实例启动   

将启动命令加入开机自启动

[root@db01 /]# echo "#mysql multi instances">>/etc/rc.local 
[root@db01 /]# echo "/application/mysql/bin/mysqld_safe --user=mysql &" >>/etc/rc.local
[root@db01 /]# echo "data/3307/mysql start" >>/etc/rc.local  
[root@db01 /]# echo "data/3306/mysql start" >>/etc/rc.local  
[root@db01 /]# tail -4 /etc/rc.local 
#mysql multi instances
/application/mysql/bin/mysqld_safe --user=mysql &
data/3307/mysql start
data/3306/mysql start

2. MySQL数据库密码设置与修改

单实例

mysqladmin -u root password 'oldboy123'         #<== 为单独数据库设置密码 
mysqladmin -uroot -poldboy123 password 123456   #<== 单独数据库修改密码

多实例

mysqladmin -S /data/3306/mysql.sock password oldboy123           #<== 为多实例（3306）设置密码
mysqladmin -S /data/3306/mysql.sock -poldboy123 password 123456  #<== 多实例密码修改

 

3. MySQL数据库单实例及多实例登录

单实例

mysql                     #<== 单实例无密码登录 
mysql -uroot -poldboy123  #<== 单实例有密码登录；root为用户、oldboy123是密码
mysql -uroot -p	          #<== 需要交互式输入密码

多实例

mysql -S /data/3306/mysql.sock		        #<== 多实例没有设置密码情况下
mysql -uroot -poldboy -S /data/3306/mysql.sock  #<== 多实例有密码情况下登录

 

4. 查看数据库版本及当前登录用户是什么 

mysql -S /data/3306/mysql.sock -e "select version();"  #<== 命令行操作，无需进入数据库
select version();          #<== 数据库里面操作
mysql -V 		   #<== 命令行操作，显示mysql版本号 
mysql --help|grep Distrib  #<== 此命令等价于mysql -V 
status;  #<== 数据库执行

 

5. MySQL数据的增删改查（删除无用的用户和数据库）数据库操作 

mysql> select user,host from mysql.user;  #<== 查看数据库用户及主机名
+----------+----------+
| user     | host     |
+----------+----------+
| root     | 127.0.0.1|
| root     | ::1      |
|          | db01     |
| root     | db01     |
|          | localhost|
| root     | localhost|
+-----------+---------+
6 rows in set (0.00 sec)
---------------以下4条删除无用的用户，只保留以上红色字体的用户即可！
mysql> drop user "root"@"::1";
mysql> drop user ""@db01;
mysql> drop user "root"@db01;
mysql> drop user ""@"localhost";
 
mysql> flush privileges;    #<== 别忘记刷新数据表
Query OK, 0 rows affected (0.00 sec)
 
mysql> show databases;      #<== 查看当前的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |·
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
mysql> drop database test;  #<== 删除tests数据库，属于安全优化
Query OK, 0 rows affected (0.16 sec)

  

---