Mysql操作

[root@localhost html]# mysql -uroot -P 3306 -p'password'
Enter password: 
#创建一个数据库
MariaDB [(none)]> create database mysqld;
Query OK, 1 row affected (0.00 sec)
# 查看数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
# 进入mysql库
MariaDB [(none)]> use mysql;
Database changed
# 查看表
MariaDB [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.00 sec)
# 增删改查之----查
MariaDB [mysql]> select * from user;
MariaDB [mysql]> select Host,User from user;
+-----------------------+------+
| Host                  | User |
+-----------------------+------+
| 127.0.0.1             | root |
| ::1                   | root |
| localhost             |      |
| localhost             | root |
| localhost.localdomain |      |
| localhost.localdomain | root |
+-----------------------+------+
6 rows in set (0.00 sec)
# 增删改查之----查(where 判断)
MariaDB [mysql]> select Host,User from user where User = 'root';
+-----------------------+------+
| Host                  | User |
+-----------------------+------+
| 127.0.0.1             | root |
| ::1                   | root |
| localhost             | root |
| localhost.localdomain | root |
+-----------------------+------+
4 rows in set (0.00 sec)

# mysql5.7
# 添加用户并授权
MariaDB [mysql]> grant all privileges on 运行操作的数据库.表 to 用户@'指定该用户在哪个主机上可以登陆' identified by '密码';
# 修改密码
MariaDB [mysql]> set password for 'root'@'%' =PASSWORD('qwe');
Query OK, 0 rows affected (0.00 sec)
# 案例  %=所有
MariaDB [mysql]> grant all privileges on *.* to test@'%' identified by '1234';
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> flush privileges;    # 立即生效
Query OK, 0 rows affected (0.00 sec)
# 查看
MariaDB [mysql]> select Host,User from user;
+-----------------------+----------+
| Host                  | User     |
+-----------------------+----------+
| %                     | test     |
| 127.0.0.1             | root     |
| localhost             | root     |
| localhost.localdomain | root     |
+-----------------------+----------+
9 rows in set (0.00 sec)
# 测试
[root@localhost html]# mysql -h 192.168.11.104 -P 3306 -utest -p'1234'

# 备份数据库
[root@localhost html]# mysqldump -h 192.168.11.104 -P 3006 -utest -p'1234' 要备份的数据库 > mysqlbak.sql
# 备份所有数据库：加在 mysqldump命令中的选项 --all-databases
# 导入数据库
[root@localhost html]# mysql -u root -p database_name < mysqlbak.sql

查看数据库： show databases;
创建数据库： create database zabbix character set utf8 collate utf8_bin;
删除数据库： drop database zabbix;


进入表：   use zabbix;
查看表：   show tables;
删除表：   drop 表名 table;


进入表：    use mysql;
查表数据： select * from user;


查看用户：              select Host,User,Password  from user;
删除用户：              drop user zabbix@'%';
创建用户设置密码： create user zabbix@'%' identified by 'password';
用户赋权：              grant all privileges on zabbix.* to zabbix@localhost;
修改用户密码：        update mysql.user set password=password('password') where user='zabbix' and host='localhost';


立即生效：   flush privileges;