linux笔记-mysql

#############
#### mysql ####
#############

#### 安装软件，配置文件
yum install mariadb-server
netstat -antple | grep mysql
vim /etc/my.cnf
*********
添加
skip-networking=1
*********

mysql_secure_installation    ##设置密码

### 登录数据库，及基本操作
mysql -u root -p
[root@wang Desktop]# mysql -u root -p
Enter password:            ##输入密码
MariaDB [(none)]> quit  ##退出

CREATE DATABASE database_name;    ##创建数据库
SHOW DATABASES;    ##显示数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+

USE database_name;    ##进入数据库
SHOW tables;            ##显示数据库中的表
CREATE TABLE table_name (name VARCHAR(20), sex CHAR(1));    ##添加表
DESCRIBE table_name;    ##查看数据库结构
INSERT INTO table_name VALUES ('wxh','M');    ##在表中添加元组
SELECT * FROM table_name;    ##查询表中所有信息
UPDATE table_name SET attribute=value WHERE attribute > value;    ##修改属性
DELETE FROM table_name WHERE attribute = value;    ##删除元组
DROP TABLE table_name;        ##删除表
DROP DATABASE database_name;    ##删除数据库

### 用户访问权限

登录数据库：

# 创建本地用户，只允许本地用户登录
CREATE USER wxh@localhost identified by 'westos';
# 创建远端访问授权的用户
CREATE USER lee@'%' identified by 'redhat';

# 用户授权
GRANT INSERT,UPDATE,DELETE,SELECT on mariadb.* to wxh@localhost;
注：给本地用户wxh添加对所有库所有表插入、更新、删除、查找的权限
GRANT SELECT on mariadb.* lee@'%';
注：给远端用户lee对所有库所有表 查找 的权限

例：
MariaDB [(none)]> select Host,User,Create_priv,Insert_priv from mysql.user;
+-----------+------+-------------+-------------+
| Host      | User | Create_priv | Insert_priv |
+-----------+------+-------------+-------------+
| localhost | root | Y           | Y           |
| localhost | wxh  | N           | N           |
| 127.0.0.1 | root | Y           | Y           |
| ::1       | root | Y           | Y           |
+-----------+------+-------------+-------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> GRANT INSERT,UPDATE,DELETE,SELECT on *.* to wxh@localhost;Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select Host,User,Create_priv,Insert_priv from mysql.user;
+-----------+------+-------------+-------------+
| Host      | User | Create_priv | Insert_priv |
+-----------+------+-------------+-------------+
| localhost | root | Y           | Y           |
| localhost | wxh  | N           | Y           |
| 127.0.0.1 | root | Y           | Y           |
| ::1       | root | Y           | Y           |
+-----------+------+-------------+-------------+
4 rows in set (0.00 sec)

重载授权表
FLUSH PRIVILEGES;
查看用户授权
SHOW GRANTS FOR wxh@localhost;
例：
MariaDB [(none)]> SHOW GRANTS FOR wxh@localhost;
+-------------------------------------------------------------------------------------------------------------------------------------+
| Grants for wxh@localhost                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'wxh'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
+-------------------------------------------------------------------------------------------------------------------------------------+

撤销用户权限
REVOKE DELETE,UPDATE,INSERT on mariadb.* from wxh@localhost;

删除用户
DROP USER wxh@localhost;


### 忘记登录密码
systemctl stop mariadb    ##先停止服务
mysqld_safe --skip-grant-tables &    ##进入安全模式
mysql -u root            ##登录系统
MariaDB [(none)]> update mysql.user set Password=password('123') where User='root';    ##设置新密码
Query OK, 0 rows affected (0.00 sec)    ##提示密码设置成功
Rows matched: 3  Changed: 0  Warnings: 0
MariaDB [(none)]> Bye    ##退出
ps aux| grep mysql    ##查看进程
kill -9 1697            ##杀死打入后台的进程
mysql -u root -p123    ##重新登录，成功


#### 数据库备份

备份
# mysqldump -uroot -ppasswd westos > westos.dump
# mysqldump -uroot -ppasswd --all-databases > backup.dump
# mysqldump -uroot -ppasswd --no-data westos > westos.dump

例：备份westos库
[root@xxxx Desktop]# mysqldump -u root -p123 westos>/mnt/westos.dump
[root@xxxx Desktop]# ls /mnt
westos.dump


恢复
# mysqladmin -uroot -ppasswd create db2
# mysql -uroot -ppasswd db2 < westos.dump

例：恢复westos数据库
先删除数据库westos
MariaDB [(none)]> drop database westos;
Query OK, 0 rows affected (0.04 sec)
备份
mysqladmin -uroot -p123 create linux
mysql -uroot -p123 linux < /mnt/westos.dump

查看
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux              |
| mysql              |
| performance_schema |
+--------------------+