mysql的安装和基本操作

CentOS Linux release 7.6.1810 (Core)

mysql版本：5.27 下载地址https://dev.mysql.com/downloads/mysql/
[root@server10 mysql7]# ls #下载安装包并安装
mysql-community-client-5.7.25-1.el7.x86_64.rpm
mysql-community-common-5.7.25-1.el7.x86_64.rpm
mysql-community-libs-5.7.25-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm //test
mysql-community-server-5.7.25-1.el7.x86_64.rpm
[root@server10 mysql7]# yum install *
[root@server10 ~]# systemctl start mysqld
[root@server10 mysql]# grep password /var/log/mysqld.log #查看初始密码

[root@server10 mysql7]# ls #下载安装包并安装
mysql-community-client-5.7.25-1.el7.x86_64.rpm
mysql-community-common-5.7.25-1.el7.x86_64.rpm
mysql-community-libs-5.7.25-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm //test
mysql-community-server-5.7.25-1.el7.x86_64.rpm
[root@server10 mysql7]# yum install *
[root@server10 ~]# systemctl start mysqld
[root@server10 mysql]# grep password /var/log/mysqld.log #查看初始密码

[root@server10 mysql]# mysql_secure_installation #初始化 #修改root密码为含大小写特殊字符不少于8位的密码然后一路回车

[root@server10 mysql]# mysql -p

mysql> show databases; #查看库

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

mysql> create database westos; #建库

Query OK, 1 row affected (0.00 sec)

mysql> use westos;

Database changed

mysql> create table linux( #建表

-> user varchar(20) not null,

-> password varchar(20) not null

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> desc linux; #查看表结构

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

| Field | Type | Null | Key | Default | Extra |

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

| user | varchar(20) | NO | | NULL | |

| password | varchar(20) | NO | | NULL | |

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

2 rows in set (0.00 sec)

mysql> insert into linux values (‘wo‘,‘134‘); #插入数据

Query OK, 1 row affected (0.00 sec

mysql> select * from linux; #查看表

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

| user | password |

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

| wo | 134 |

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

1 row in set (0.00 sec
用户授权：

mysql> create user guoyanfeng@‘%‘ identified by ‘XR@westos123.com‘; #创建用户
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert on westos.* to guoyanfeng@‘%‘; #授权到库
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for guoyanfeng@‘%‘; #查看权限

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

| Grants for guoyanfeng@% |

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

| GRANT USAGE ON . TO ‘guoyanfeng‘@‘%‘ |

| GRANT SELECT, INSERT ON westos.* TO ‘guoyanfeng‘@‘%‘ |

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

2 rows in set (0.00 sec)

mysql> grant all privileges on . to guoyanfeng@‘%‘; #用户所有库的满权限

mysql> revoke insert,select on westos.* from guoyanfeng@‘%‘; #撤销权限

mysql> show grants for guoyanfeng@‘%‘;

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

| Grants for guoyanfeng@% |

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

| GRANT ALL PRIVILEGES ON . TO ‘guoyanfeng‘@‘%‘ |

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

mysql> use mysql;

mysql> update mysql.user set authentication_string=password(‘XR&westos123.com‘) where user=‘guoyanfeng‘ #改用户密码

mysql> flush privileges; #刷新

mysqldump -uroot -pwestos--all-databases ##备份所有数据库

mysqldump -uroot -pwestos --all-database--no-data ##备份所有数据库框架

[root@server10 ~]# mysqldump -u root -pCaonimei@478 --all-databases > /mnt/all.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure. #备份会报错但不影响备份，原因为mysql版本问题须在my.cnf文件中添加用户名和密码执行备份
mysql跳过密码登录:

1.在 etc/my.cnf 的 [mysqld] 下添加 skip-grant-tables

2.重启mysql服务即可.
mysql遇到不可解决的问题时：#慎用

备份所有数据库，确定无误。

[root@server10 mysql]# systemctl stop mysqld.service #停止数据库

[root@server10 mysql]# cd /var/lib/mysql #默认路径

[root@server10 mysql]# rm -fr * #删除所有，重新初始化

mysql的安装和基本操作

标签：结构   auth   添加用户   初始化   mys   rman   文件   create   x86_64