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 | +--------------------+