关于Linux下MYSQL的安装部署以及sysbench测试

MYSQL的安装

环境：redhat6.8服务器

版本：mysql5.6.39版本

下载地址：https://dev.mysql.com/downloads/mysql/5.6.html

解压并用rpm安装

查看mysql的版本命令：$mysql -V

启动mysql命令：$service mysql start

MYSQL的目录信息

默认数据库所在目录：/var/lib/mysql     备注：存放数据库，即datadir目录，sock文件，pid文件

默认加载配置文件：/etc/my.cnf    备注：可以从示例配置文件中拷贝过来，修改其中的内容

[mysqld]
port=3306    //用于远程连接的端口
#datadir=/var/lib/mysql/    //默认的数据目录
datadir=/home/mysql/        //修改后的数据目录
socket=/var/lib/mysql/mysql.sock    //存放socket文件
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

max_connections=16384    //最大连接数，sysbench需要用

其他文件所在目录：/usr/share/mysql    备注：存放示例配置文件my-default.cnf等

MYSQL的使用

命令：

查看mysql版本：mysql --version

[root@stor37 cmm]# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

进入mysql命令：$mysql -u 用户名 -p 密码

[root@stor37 cmm]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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> 

查看数据库：>show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

新建数据库：>create database [数据库名称];

mysql> create database newtwo;
Query OK, 1 row affected (0.00 sec)

mysql>

删除数据库：>drop databse [数据库名称]；

mysql> drop database newtwo;
Query OK, 0 rows affected (0.02 sec)

mysql>

使用数据库：>use [数据库名称];

mysql> use newone;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

查看数据库下的表：>show tables;

mysql> show tables;
+------------------+
| Tables_in_newone |
+------------------+
| abc              |
+------------------+
1 row in set (0.00 sec)

mysql>

建立新表：>create table [表名](属性);

mysql> create table efg(num int);
Query OK, 0 rows affected (0.04 sec)

mysql>

删除旧表：>drop table [表名];

mysql> drop table efg;
Query OK, 0 rows affected (0.00 sec)

mysql>

查看表的结构：>desc [表名];

mysql> desc abc;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| high    | int(11)     | YES  |     | NULL    |       |
| address | varchar(20) | YES  |     | null    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

查找数据：>select * from [表名];

mysql> select * from abc;
+------+------+------+---------+
| name | age  | high | address |
+------+------+------+---------+
| cc   |   24 |  170 | null    |
+------+------+------+---------+
1 row in set (0.00 sec)

mysql>

插入数据：>insert into [表名]（表的属性）;

mysql> insert into newtable values(5);
Query OK, 1 row affected (0.00 sec)

mysql> 

修改数据：>update [表名] set name = "abc";

mysql> update abc set name="bcd";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from abc;
+------+------+------+---------+
| name | age  | high | address |
+------+------+------+---------+
| bcd  |   24 |  170 | null    |
+------+------+------+---------+
1 row in set (0.00 sec)
mysql>

删除数据：>delete [表名] where name = "abc";

修改密码：>set password for 用户=password('新密码')；（备注：其他方法见参考网址）

mysql> set password for root@localhost = password('123456');
Query OK, 0 rows affected (0.00 sec)

mysql> 

MYSQL数据挂载到磁盘上

格式化磁盘，挂载到目录上

[root@stor37 home]# mkfs.ext4 /dev/lun_mysql2
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
19660800 inodes, 78643200 blocks
3932160 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
2400 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
	32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
	4096000, 7962624, 11239424, 20480000, 23887872, 71663616

Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 24 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
mount /dev/lun_mysql2 2

拷贝/var/lib/mysql/*到datadir下

[root@stor37 home]# cp -rp /var/lib/mysql/* 2/

查看挂载目录的权限，如果不是mysql需修改

[root@stor37 home]# ll
total 161964
drwxr-xr-x. 4 mysql mysql      4096 Mar 15 11:10 1
drwxr-xr-x. 5 root  root       4096 Mar 15 11:31 2
[root@stor37 home]# chown -R mysql:mysql 2
[root@stor37 home]# ll
total 161964
drwxr-xr-x. 4 mysql mysql      4096 Mar 15 11:10 1
drwxr-xr-x. 5 mysql mysql      4096 Mar 15 11:31 2

修改/etc/my.cnf的datadir（也可以建立软链接就不需要修改my.cnf）

[root@stor37 home]# vim /etc/my.cnf 
[mysqld]
port=3306
#datadir=/var/lib/mysql/
datadir=/home/2/
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重新启动mysqld

[root@stor37 home]# service mysqld start
Starting mysqld:                                           [  OK  ]

MYSQL性能测试——sysbench实践

下载安装sysbench

下载地址：https://github.com/akopytov/sysbench

编译安装：

./autogen.sh
./configure 
make
make install

bin目录在/usr/local/bin/目录下

share目录在/usr/local/share/sysbench/目录下

sysbench流程

初始化数据

[root@stor37 bin]# sysbench --test=/usr/local/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-db=newone --mysql-password=123456 --max-requests=0 --table_size=1000000 --threads=128 prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
[root@stor37 bin]# 

参数	含义
--test=	运行导数据的脚本
--tables	测试需要几张表
--table-size	每张表的大小
--mysql-host	mysql host
--mysql-port	mysql port
--mysql-db	mysql db
--mysql-user	mysql user
--mysql-password	mysql password
--rand-init	是否随机初始化数据
--max-requests	执行多少个请求后停止
prepare	执行导数据

运行测试

[root@stor37 bin]# sysbench --test=/usr/local/share/sysbench/oltp_read_write.lua --report-interval=10 --rand-type=uniform --mysql-user=root --mysql-password=123456 --mysql-db=newone --table_size=1000000 --threads=128 --time=1000 --max-requests=0 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

参数	含义
--test=	需要运行的lua脚本
--tables	测试需要几张表
--table-size	每张表的大小
--threads	测试并发线程数
--oltp-read-only	是否只读测试
--report-interval	结果输出间隔
--rand-type	数据分布模式，热点数据或随机数据
--max-time	最大运行时间
--max-requests	执行多少个请求后停止
run	开始测试

[10s]运行时间点

Threads：100100个线程数

tps：378.90每秒执行378.9个事务

reads：读请求数

writes：写请求数

response time：响应时间

errors：错误个数

reconnects：重新连接个数

清理环境

[root@stor37 bin]# sysbench --test=/usr/local/share/sysbench/oltp_read_write.lua --report-interval=10 --rand-type=uniform --mysql-user=root --mysql-password=123456 --mysql-db=newone --time=1000 --max-requests=0 cleanup
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'...

总结：

操作流程就是把默认数据目录下的文件该拷贝的拷贝出来到新的数据目录，然后修改配置文件的datadir为新的数据目录，socket保持原先状态，启动的时候会在原数据目录下创建sock文件，database在新目录下，新目录的权限修改成mysql，selinux的权限也改成0，启动mysql服务就行了

附录：

MYSQL启动失败问题及解决方案：

1.查看/var/log/mysql.log文件can't change dir to 'XXX' (Errorcode: 13)

（备注：5.1版本的出错信息在/var/log/mysql.log,而5.6版本的出错信息在datadir目录下）

180315 11:26:07 mysqld_safe Starting mysqld daemon with databases from /home/2/
180315 11:26:07 [Warning] Can't create test file /home/2/stor37.lower-test
180315 11:26:07 [Warning] Can't create test file /home/2/stor37.lower-test
^G/usr/libexec/mysqld: Can't change dir to '/home/2/' (Errcode: 13)
180315 11:26:07 [ERROR] Aborting

180315 11:26:07 [Note] /usr/libexec/mysqld: Shutdown complete

180315 11:26:07 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

解决方案：

[root@stor37 home]# getenforce
Enforcing
[root@stor37 home]# setenforce 0
[root@stor37 home]# getenforce
Permissive
[root@stor37 home]# service mysqld start
Starting mysqld:                                           [  OK  ]

2.描述：从默认的/var/lib/mysql目录下拷贝出来的文件放在自己新建的datadir目录下产生的错误，错误信息是无法创建pid，datadir目录的权限也给成mysql，启动还是失败getenforce没有设置成0，用setenforce 0设置成0以后mysql启动成功

[root@rhel213 home]# service mysql start
Starting MySQL.The server quit without updating PID file (/[FAILED]ql//rhel213.pid).

解决方法：getenforce没有设置成0，用setenforce 0设置成0以后mysql启动成功

[root@rhel213 lib]# getenforce
Enforcing
[root@rhel213 lib]# setenforce 0
[root@rhel213 lib]# getenforce
Permissive
[root@rhel213 lib]# service mysql start
Starting MySQL.                                            [  OK  ]

3.描述：/var/lib/mysql下文件缺失，比如说文件误删除等

解决方法：执行mysql_install_db会在/var/lib/mysql目录下重新生成所有的文件

参考：

https://www.cnblogs.com/tianlangshu/p/5665290.html

http://blog.youkuaiyun.com/stubborn_cow/article/details/47906133

http://www.jb51.net/article/26505.htm

https://www.baidu.com/link?url=WPeAhbF8MKrJy8WWsBg2sDr3QgkyyCvw2HbriijJELZueq4Og5Qu8iEpt9cbV_Pizs-L0CfSY-zm-sOxtsJcP_&wd=&eqid=a4d01cb40002c1d9000000025aab3976