mysql的安装、配置、优化

mysql的安装、配置、优化

通过yum的方式来进行mysql的数据库安装

1、输入命令 yum list | grep mysql查看yum上提供的MySQL数据库可下载的版本

[root@izbp12ujq5zpxyjm1g69dhz ~]# yum list | grep mysql
akonadi-mysql.x86_64                    1.9.2-4.el7                    base     
apr-util-mysql.x86_64                   1.5.2-6.el7                    base     
calligra-kexi-driver-mysql.x86_64       2.9.10-2.el7                   epel     
collectd-mysql.x86_64                   5.8.1-1.el7                    epel     
dmlite-plugins-mysql.x86_64             1.13.2-2.el7                   epel     
dovecot-mysql.x86_64                    1:2.2.36-3.el7_7.1             updates  
dpm-copy-server-mysql.x86_64            1.13.0-1.el7                   epel     
dpm-name-server-mysql.x86_64            1.13.0-1.el7                   epel     
dpm-server-mysql.x86_64                 1.13.0-1.el7                   epel     
dpm-srm-server-mysql.x86_64             1.13.0-1.el7                   epel     
dspam-mysql.x86_64                      3.10.2-12.el7                  epel     
exim-mysql.x86_64                       4.92.3-1.el7                   epel     
freeradius-mysql.x86_64                 3.0.13-10.el7_6                base     
fts-mysql.x86_64                        3.8.4-2.el7                    epel     
gnokii-smsd-mysql.x86_64                0.6.31-17.el7                  epel     
holland-mysql.noarch                    1.1.18-1.el7                   epel     
holland-mysqldump.noarch                1.1.18-1.el7                   epel     
holland-mysqllvm.noarch                 1.1.18-1.el7                   epel     
lfc-server-mysql.x86_64                 1.13.0-1.el7                   epel     
libdbi-dbd-mysql.x86_64                 0.8.3-16.el7                   base     
libgda-mysql.x86_64                     1:5.2.2-7.el7                  epel     
libnss-mysql.x86_64                     1.5-19.el7                     epel     
libodb-mysql.x86_64                     2.3.0-1.el7                    epel     
libodb-mysql-devel.x86_64               2.3.0-1.el7                    epel     
lighttpd-mod_authn_mysql.x86_64         1.4.54-1.el7                   epel     
lighttpd-mod_mysql_vhost.x86_64         1.4.54-1.el7                   epel     
mysql++.x86_64                          3.1.0-12.el7                   epel     
mysql++-devel.x86_64                    3.1.0-12.el7                   epel     
mysql++-manuals.x86_64                  3.1.0-12.el7                   epel     
mysql-connector-java.noarch             1:5.1.25-3.el7                 base     
mysql-connector-odbc.x86_64             5.2.5-8.el7                    base     
mysql-connector-python.noarch           1.1.6-1.el7                    epel     
mysql-mmm.noarch                        2.2.1-15.el7                   epel     
mysql-mmm-agent.noarch                  2.2.1-15.el7                   epel     
mysql-mmm-monitor.noarch                2.2.1-15.el7                   epel     
mysql-mmm-tools.noarch                  2.2.1-15.el7                   epel     
mysql-proxy.x86_64                      0.8.5-2.el7                    epel     
mysql-proxy-devel.x86_64                0.8.5-2.el7                    epel     
mysql-utilities.noarch                  1.3.6-1.el7                    epel     
mysqlreport.noarch                      3.5-11.el7                     epel     
mysqltuner.noarch                       1.7.13-1.git.59e5f40.el7       epel     
nagios-plugins-mysql.x86_64             2.2.2-2.20190926git1b8ad57.el7 epel     
nextcloud-mysql.noarch                  10.0.4-2.el7                   epel     
nodejs-mysql.noarch                     2.3.2-1.el7                    epel     
opendbx-mysql.x86_64                    1.4.6-6.el7                    epel     
opensips-mysql.x86_64                   1.10.5-4.el7                   epel     
owncloud-mysql.noarch                   9.1.5-1.el7                    epel     
pcp-pmda-mysql.x86_64                   4.3.2-3.el7_7                  updates  
pdns-backend-mysql.x86_64               4.1.11-1.el7                   epel     
perdition-mysql.x86_64                  2.2-2.el7                      epel     
perl-Test-mysqld.noarch                 0.21-1.el7                     epel     
php-mysql.x86_64                        5.4.16-46.1.el7_7              updates  
php-mysqlnd.x86_64                      5.4.16-46.1.el7_7              updates  
php-pear-MDB2-Driver-mysql.noarch       1.5.0-0.8.b4.el7               epel     
php-pear-MDB2-Driver-mysqli.noarch      1.5.0-0.8.b4.el7               epel     
poco-mysql.x86_64                       1.6.1-3.el7                    epel     
preludedb-mysql.x86_64                  5.0.0-1.el7                    epel     
proftpd-mysql.x86_64                    1.3.5e-7.el7                   epel     
python36-mysql.x86_64                   1.3.12-2.el7                   epel     
python36-mysql-debug.x86_64             1.3.12-2.el7                   epel     
qt-mysql.i686                           1:4.8.7-3.el7_6                base     
qt-mysql.x86_64                         1:4.8.7-3.el7_6                base     
qt5-qtbase-mysql.i686                   5.9.7-2.el7                    base     
qt5-qtbase-mysql.x86_64                 5.9.7-2.el7                    base     
redland-mysql.x86_64                    1.0.16-6.el7                   base     
root-sql-mysql.x86_64                   6.18.04-1.el7                  epel     
rsyslog-mysql.x86_64                    8.24.0-41.el7_7.2              updates  
soci-mysql.x86_64                       3.2.3-1.el7                    epel     
soci-mysql-devel.x86_64                 3.2.3-1.el7                    epel     
tcl-mysqltcl.x86_64                     3.052-1.el7                    epel     
voms-mysql-plugin.x86_64                3.1.7-5.el7                    epel     
wt-dbo-mysql.x86_64                     3.3.9-1.el7                    epel     
zabbix22-dbfiles-mysql.noarch           2.2.23-1.el7                   epel     
zabbix22-proxy-mysql.x86_64             2.2.23-1.el7                   epel     
zabbix22-server-mysql.x86_64            2.2.23-1.el7                   epel     
zabbix22-web-mysql.noarch               2.2.23-1.el7                   epel     
zabbix30-dbfiles-mysql.noarch           3.0.22-2.el7                   epel     
zabbix30-proxy-mysql.x86_64             3.0.22-2.el7                   epel     
zabbix30-server-mysql.x86_64            3.0.22-2.el7                   epel     
zabbix30-web-mysql.noarch               3.0.22-2.el7                   epel     
zabbix40-dbfiles-mysql.noarch           4.0.12-1.el7                   epel     
zabbix40-proxy-mysql.x86_64             4.0.12-1.el7                   epel     
zabbix40-server-mysql.x86_64            4.0.12-1.el7                   epel     
zabbix40-web-mysql.noarch               4.0.12-1.el7                   epel     
[root@izbp12ujq5zpxyjm1g69dhz ~]# 

2、然后输入yum install -y mysql-server mysql mysql-devel 命令将mysql mysql-server mysql-devel都安装好

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

3、启动MySQL服务，输入service mysqld start命令可启动MySQL服务。

[root@izbp12ujq5zpxyjm1g69dhz ~]# service mysqld start
初始化 MySQL 数据库： WARNING: The host 'xiaoluo' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
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 xiaoluo 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![确定]
正在启动 mysqld：                                            [确定]

4、现在我们可以查看一下MySQL服务是否为开机自启动

命令：chkconfig --list | grep mysqld

[root@izbp12ujq5zpxyjm1g69dhz ~]# chkconfig --list | grep mysqld
mysqld             0:关闭    1:关闭    2:关闭    3:关闭    4:关闭    5:关闭    6:关闭

此时mysqld并不是开机自启动，我们可以将其设置为开机自启动。
命令：chkconfig mysqld on

[root@izbp12ujq5zpxyjm1g69dhz ~]# chkconfig mysqld on
[root@izbp12ujq5zpxyjm1g69dhz ~]# chkconfig --list | grep mysql
mysqld             0:关闭    1:关闭    2:启用    3:启用    4:启用    5:启用    6:关闭

这样我们就不必每次都手动启动了。

5、MySQL 数据库安装完以后只会有一个root管理员账号，此时的root账号并没有密码，所以需要给我们的root账号设置密码。

[root@izbp12ujq5zpxyjm1g69dhz ~]# mysqladmin -u root password 'root02'　//设置密码为root02

此时可以使用账号密码登陆mysql数据了
命令： mysql -u root -p**

[root@izbp12ujq5zpxyjm1g69dhz ~]mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>

6、如何修改root密码

方法1： 用SET PASSWORD命令

　　mysql -u root

　　mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

方法2：用mysqladmin
已设置密码：mysqladmin -u root password "newpass"
未设置密码：mysqladmin -u root password oldpass "newpass"
方法3： 用UPDATE直接编辑user表

　　mysql -u root

　　mysql> use mysql;

　　mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';

　　mysql> FLUSH PRIVILEGES;

在丢失root密码的时候，可以这样

　　mysqld_safe --skip-grant-tables&

　　mysql -u root mysql

　　mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';

　　mysql> FLUSH PRIVILEGES;