【MySQL】MySQL在CentOS的搭建

安装mysql

查询yum服务器上可用的关于mysql的安装包:

[root@localhost ~]# yum list | grep mysql
mysql-libs.x86_64                           5.1.71-1.el6                @anaconda-CentOS-201311272149.x86_64/6.5
apr-util-mysql.x86_64                       1.3.9-3.el6_0.1             base    
bacula-director-mysql.x86_64                5.0.0-13.el6                base    
bacula-storage-mysql.x86_64                 5.0.0-13.el6                base    
dovecot-mysql.x86_64                        1:2.0.9-22.el6              base    
freeradius-mysql.x86_64                     2.2.6-6.el6_7               base    
libdbi-dbd-mysql.x86_64                     0.8.3-5.1.el6               base    
mod_auth_mysql.x86_64                       1:3.0.0-11.el6_0.1          base    
mysql.x86_64                                5.1.73-8.el6_8              base    
mysql-bench.x86_64                          5.1.73-8.el6_8              base    
mysql-connector-java.noarch                 1:5.1.17-6.el6              base    
mysql-connector-odbc.x86_64                 5.1.5r1144-7.el6            base    
mysql-devel.i686                            5.1.73-8.el6_8              base    
mysql-devel.x86_64                          5.1.73-8.el6_8              base    
mysql-embedded.i686                         5.1.73-8.el6_8              base    
mysql-embedded.x86_64                       5.1.73-8.el6_8              base    
mysql-embedded-devel.i686                   5.1.73-8.el6_8              base    
mysql-embedded-devel.x86_64                 5.1.73-8.el6_8              base    
mysql-libs.i686                             5.1.73-8.el6_8              base    
mysql-libs.x86_64                           5.1.73-8.el6_8              base    
mysql-server.x86_64                         5.1.73-8.el6_8              base    
mysql-test.x86_64                           5.1.73-8.el6_8              base    
pcp-pmda-mysql.x86_64                       3.10.9-9.el6                base    
php-mysql.x86_64                            5.3.3-49.el6                base    
qt-mysql.i686                               1:4.6.2-28.el6_5            base    
qt-mysql.x86_64                             1:4.6.2-28.el6_5            base    
rsyslog-mysql.x86_64                        5.8.10-10.el6_6             base    
rsyslog7-mysql.x86_64                       7.4.10-7.el6                base    

安装：

[root@localhost ~]# yum install -y mysql-server mysql mysql-devel

查询是否安装好：

[root@localhost ~]# rpm -qa | grep mysql

启动服务：

[root@localhost ~]# service mysqld start
Initializing MySQL database:  Installing MySQL system tables...
OK
Filling help tables...
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 MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
/usr/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 manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

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

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

登录

设置root用户的密码：

[root@localhost ~]# /usr/bin/mysqladmin -u root password '123456'

根据设置的密码登录：

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
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> 

创建新的用户nick-huang，允许远程登录，并授予权限：

grant all privileges on *.* to nick-huang @"%" identified by '123456';
flush privileges;

如果登录权限有问题，可以用安全模式登录并设置好权限：

[root@localhost ~]# service mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@localhost ~]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
[1] 3766
[root@localhost ~]# 170409 06:37:18 mysqld_safe Logging to '/var/log/mysqld.log'.
170409 06:37:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@localhost ~]# 
[root@localhost ~]# 
[root@localhost ~]# mysql -u root

然后就可以通过SQL修改mysql.user表的字段。
比如：

• 修改密码：mysql> update mysql.user set password = password('123456') where user = 'root';
• 修改登录主机：修改host字段为%，则可在任意IP的主机连接MySQL

设置完后重启mysqld服务：

[root@localhost ~]# service mysqld stop

修改监听端口

备份配置文件，然后修改：

cp /etc/my.cnf /etc/my.cnf.bak.20170808
vi /etc/my.cnf

在[mysqld]下添加：port=30000
然后重启：service mysqld restart

状态的查询

SHOW SESSION STATUS;或SHOW STATUS;，查询当前会话的状态
SHOW GLOBAL STATUS;，查询全局的状态
具体的解析可参考MySQL运行状态show status中文详解

当然，状态太多，可以通过like查找，比如查询最大已用连接数：SHOW GLOBAL STATUS LIKE 'Max_used_connections';

其它

参数设置

查询

SHOW SESSION VARIABLES;或SHOW VARIABLES;，查询当前会话的参数
SHOW GLOBAL VARIABLES;，查询全局的参数

也可以模糊查询，比如查询允许最大连接数：SHOW GLOBAL VARIABLES LIKE '%max_connections%';

设置参数

例子，设置连接空闲超时时间为2200秒：set global wait_timeout = 2200;，关于wait_timeout参数，有经典的8小时问题，详情请参考：mysql经典的8小时问题-wait_timeout

连接进程

查询

通过SHOW PROCESSLIST;或SHOW FULL PROCESSLIST;查看连接进程列表。实际的数据可以通过查询具体表得到：

select * from information_schema.`PROCESSLIST` t;

比如，查询各主机的连接进程数量：

SELECT SUBSTRING_INDEX(t.`HOST`, ':', 1), COUNT(*), GROUP_CONCAT(t.`COMMAND`) FROM information_schema.`PROCESSLIST` t GROUP BY SUBSTRING_INDEX(t.`HOST`, ':', 1) ORDER BY SUBSTRING_INDEX(t.`HOST`, ':', 1);

比如，查询某主机连接各库的进程数量：

SELECT t.`DB`, COUNT(*) FROM information_schema.`PROCESSLIST` t WHERE t.`HOST` LIKE '10.0.76.34%' GROUP BY t.`DB`;

删除连接进程

可以通过语句删除指定的连接进程：kill connection 进程ID（SHOW PROCESSLIST的ID列）;