linux下安装配置mysql

（1）yum安装方法：

yum install mariadb mariadb-server mariadb-libs -y     # linux 7.x环境

yum install mysql-server mysql-devel mysql-libs -y   # linux 6.x环境

（2）源码安装：

环境：Redhat 7.4  、mysql-5.5.20

tar -zxvf mysql-5.5.20.tar.gz  #解压

cd mysql-5.5.20  #进到目录

yum install gcc gcc-c++ cmake ncurses-devel ncurses -y  #安装依赖包，ncurses是字符处理库

#从MySQL5.5版本开始,MySQL源码安装的编译工具由configure开始向cmake过渡

[root@xsbfwbtest mysql-5.5.20]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql55/ \   #预编译路径
> -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \   #socket通信文件
> -DMYSQL_DATADIR=/software/mysql \     #数据存储路径
> -DSYSCONFDIR=/etc \                   #配置文件路径
> -DMYSQL_USER=mysql \                  #运行用户
> -DMYSQL_TCP_PORT=3306 \               #运行时的监听端口
> -DWITH_PARTITION_STORAGE_ENGINE=1 \   #开启PARTITION引擎
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \   #开启BLACKHOLE引擎
> -DWITH_XTRADB_STORAGE_ENGINE=1 \      #开启XTRADB引擎
> -DWITH_MYISAM_STORAGE_ENGINE=1 \      #开启MYISAM引擎
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \    #开启INNOBASE引擎
> -DWITH_READLINE=1 \                   #打开快捷键功能
> -DENABLED_LOCAL_INFILE=1 \            #允许从本地导数据
> -DWITH_EXTRA_CHARSETS=1 \             #支持额外字符集
> -DDEFAULT_CHARSET=utf8 \              #默认字符集
> -DDEFAULT_COLLATION=utf8_general_ci \ #检验字符
> -DEXTRA_CHARSETS=all \                #加载所有扩展字符集
> -DWITH_BIG_TABLES=1 \                 #将临时表存储到磁盘
> -DWITH_DEBUG=0                        #禁止调试模式
[root@xsbfwbtest mysql-5.5.20]# make  #编译
......
[root@xsbfwbtest mysql-5.5.20]# make install  #安装
......

 

[root@xsbfwbtest mysql-5.5.20]# cd /usr/local/mysql55/
[root@xsbfwbtest mysql55]# \cp support-files/my-large.cnf /etc/my.cnf
[root@xsbfwbtest mysql55]# \cp support-files/mysql.server /etc/init.d/mysqld
[root@xsbfwbtest mysql55]# chkconfig --add mysqld
[root@xsbfwbtest mysql55]# chkconfig --level 35 mysqld on
[root@xsbfwbtest mysql55]# mkdir -p /software/mysql
[root@xsbfwbtest mysql55]# useradd mysql
[root@xsbfwbtest mysql55]# ln -s /usr/local/mysql55/bin/* /usr/bin/
[root@xsbfwbtest mysql55]# /usr/local/mysql55/scripts/mysql_install_db --user=mysql --datadir=/software/mysql/ --basedir=/usr/local/mysql55/
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/local/mysql55//bin/mysqladmin -u root password 'new-password'
/usr/local/mysql55//bin/mysqladmin -u root -h xsbfwbtest password 'new-password'

Alternatively you can run:
/usr/local/mysql55//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/local/mysql55/ ; /usr/local/mysql55//bin/mysqld_safe &

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

Please report any problems with the /usr/local/mysql55//scripts/mysqlbug script!

[root@xsbfwbtest mysql55]# service mysqld start   #上面已经设置为系统服务，所以直接启动
Starting MySQL... SUCCESS!
[root@xsbfwbtest mysql55]# service mysqld status   #mysql已经启动了
 SUCCESS! MySQL running (27377)
[root@xsbfwbtest mysql55]# ps -ef | grep mysql     #进程也出来了
root     27102     1  0 17:34 pts/2    00:00:00 /bin/sh /usr/local/mysql55/bin/mysqld_safe --datadir=/software/mysql --pid-file=/software/mysql/xsbfwbtest.pid
mysql    27377 27102  0 17:34 pts/2    00:00:00 /usr/local/mysql55/bin/mysqld --basedir=/usr/local/mysql55 --datadir=/software/mysql --plugin-dir=/usr/local/mysql55/lib/plugin --user=mysql --log-error=/software/mysql/xsbfwbtest.err --pid-file=/software/mysql/xsbfwbtest.pid --socket=/tmp/mysql.sock --port=3306
root     27445 14186  0 17:37 pts/2    00:00:00 grep --color=auto mysql
[root@xsbfwbtest mysql55]# service mysqld stop   #先停了，下面试下另一种方式启动
Shutting down MySQL. SUCCESS!
[root@xsbfwbtest mysql55]# /usr/local/mysql55/bin/mysqld_safe --user=mysql --datadir=/software/mysql/ --basedir=/usr/local/mysql55/ &   #上面不设置成系统服务的话就直接用命令启动
[1] 29239
[root@xsbfwbtest mysql55]# 190718 17:51:17 mysqld_safe Logging to '/software/mysql//xsbfwbtest.err'.
190718 17:51:17 mysqld_safe Starting mysqld daemon with databases from /software/mysql/

[root@xsbfwbtest mysql55]# /etc/init.d/mysqld status  #看下已经起来了
 SUCCESS! MySQL running (29525)
[root@xsbfwbtest mysql55]# netstat -tlnp | grep 3306   #监听端口也出来咯
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      29525/mysqld
[root@xsbfwbtest mysql55]#

 下面玩下一下基本命令：

[root@xsbfwbtest mysql55]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.20-log Source distribution

Copyright (c) 2000, 2011, 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> create database sbwb;    #创建一个数据库，名为sbwb
Query OK, 1 row affected (0.01 sec)

mysql> use sbwb;  #进入数据库
Database changed
mysql> show tables;   #查看该库有几张表
Empty set (0.00 sec)

mysql> create table a1 (id varchar(10),name varchar(10)); #建一张名为a1的表，设字段和长度
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a1 values ("1","test");  #向表插入数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from a1;    #查看表的内容
+------+------+
| id   | name |
+------+------+
| 1    | test |
+------+------+
1 row in set (0.00 sec)

mysql> select * from a1 where name='test';  #加个条件去查询
+------+------+
| id   | name |
+------+------+
| 1    | test |
+------+------+
1 row in set (0.00 sec)

mysql> desc a1;  #查看a1表字段
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table a1 modify column name varchar(20);  #修改表字段
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc a1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> update a1 set name='debug' where id=1;   #更新表字段内容
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from a1;
+------+-------+
| id   | name  |
+------+-------+
| 1    | debug |
+------+-------+
1 row in set (0.00 sec)

mysql> delete from a1;   #清空表内容
Query OK, 1 row affected (0.01 sec)

mysql> select * from a1;  
Empty set (0.00 sec)

mysql> drop table a1;   #删除表
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> drop database sbwb;  #删除库
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> show engines;   #查看存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

mysql> show variables like '%storage_engine%';  #查看默认存储引擎
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)

mysql> show variables like '%char%';  #查看字符集
+--------------------------+------------------------------------+
| Variable_name            | Value                              |
+--------------------------+------------------------------------+
| character_set_client     | utf8                               |
| character_set_connection | utf8                               |
| character_set_database   | utf8                               |
| character_set_filesystem | binary                             |
| character_set_results    | utf8                               |
| character_set_server     | utf8                               |
| character_set_system     | utf8                               |
| character_sets_dir       | /usr/local/mysql55/share/charsets/ |
+--------------------------+------------------------------------+
8 rows in set (0.00 sec)

mysql> alter table a1 engine=innodb;  #修改表a1存储引擎,不过刚才a1被我干掉了，所以报错
ERROR 1046 (3D000): No database selected
mysql> quit   #退出
Bye
[root@xsbfwbtest mysql55]#

下面修改密码：

[root@xsbfwbtest mysql55]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.20-log Source distribution

Copyright (c) 2000, 2011, 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.01 sec)

mysql> use mysql;   #用户和密码都放在mysql库中的user表
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.01 sec)

mysql> update user set password = password('123456') where user='root';  #修改root密码
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;   #刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@xsbfwbtest mysql55]# mysql -u root -p  #使用root用户
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.20-log Source distribution

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

创建用户以及授权

mysql> create database debug; 
Query OK, 1 row affected (0.01 sec)

mysql> grant all on debug.* to test@"%" identified by '123passwd';  #授权所有主机("%")通过test用户和密码('123passwd')访问debug库的所有权限(all)
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,update,delete on *.* to test@localhost identified by '123passwd';  #授权localhost主机通过test用户和密码('123passwd')访问debug库的查询，插入，更新，删除权限
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on debug.* to test@'192.168.0.2' identified by '123passwd';  #授权192.168.0.2主机通过test用户和密码('123passwd')访问debug库的所有权限(all)
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;   #别忘了要刷新权限才会生效
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;  #现在试下给root授权可以远程访问
Database changed

mysql> grant all privileges on *.* to 'root'@'%' identified by '312passwd' with grant option;   #WITH GRANT OPTION 表示允许级联授权。
Query OK, 0 rows affected (0.00 sec)

mysql> select User,authentication_string,Host from user where User='root';
+------+-----------------------+-------------+
| User | authentication_string | Host        |
+------+-----------------------+-------------+
| root |                       | localhost   |
| root |                       | XSBFWBCSHJ1 |
| root |                       | 127.0.0.1   |
| root |                       | ::1         |
| root | NULL                  | %           |   %代表所有的地址可以访问
+------+-----------------------+-------------+
5 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

[root@xsbfwbtest mysql55]# getenforce
Enforcing
[root@xsbfwbtest mysql55]# setenforce 0  #临时关闭se
[root@xsbfwbtest mysql55]# getenforce
Permissive
[root@xsbfwbtest mysql55]# vim /etc/selinux/config   #配置开机关闭se,SELINUX=disabled
[root@xsbfwbtest mysql55]# systemctl stop firewalld   #关闭防火墙
[root@xsbfwbtest mysql55]# systemctl status firewalld  #查看防火墙状态
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Thu 2019-07-18 19:27:14 CST; 1min 6s ago
     Docs: man:firewalld(1)
 Main PID: 850 (code=exited, status=0/SUCCESS)

Jul 17 19:06:33 xsbfwbtest systemd[1]: Starting firewalld - dynamic firewall daemon...
Jul 17 19:06:34 xsbfwbtest systemd[1]: Started firewalld - dynamic firewall daemon.
Jul 17 19:06:35 xsbfwbtest firewalld[850]: WARNING: ICMP type 'beyond-scope' is not supported by the kernel for ipv6.
Jul 17 19:06:35 xsbfwbtest firewalld[850]: WARNING: beyond-scope: INVALID_ICMPTYPE: No supported ICMP type., ignoring for run-time.
Jul 17 19:06:35 xsbfwbtest firewalld[850]: WARNING: ICMP type 'failed-policy' is not supported by the kernel for ipv6.
Jul 17 19:06:35 xsbfwbtest firewalld[850]: WARNING: failed-policy: INVALID_ICMPTYPE: No supported ICMP type., ignoring for run-time.
Jul 17 19:06:35 xsbfwbtest firewalld[850]: WARNING: ICMP type 'reject-route' is not supported by the kernel for ipv6.
Jul 17 19:06:35 xsbfwbtest firewalld[850]: WARNING: reject-route: INVALID_ICMPTYPE: No supported ICMP type., ignoring for run-time.
Jul 18 19:27:13 xsbfwbtest systemd[1]: Stopping firewalld - dynamic firewall daemon...
Jul 18 19:27:14 xsbfwbtest systemd[1]: Stopped firewalld - dynamic firewall daemon.