授权示例： MariaDB [(none)]> GRANT ALL ON root.TO 'root'@'192.168.40.133' IDENTIFIED BY '123'; 权限读取： MariaDB [(none)]> FLUSH privileges; 1,mysql -uroot -p -hIP -p端口 [root@localhost ~]# mysql -uroot -p'123' -h192.168.40.133 -P3306 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 34 Server version: 5.5.45-MariaDB-log Source distribution Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> 2,查看当前库 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> 3，查询某个库，需要切换进去，而后在列出来 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) 4，查看表的全部字段 MariaDB [mysql]> desc slow_log; +----------------+------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +----------------+------------------+------+-----+-------------------+-----------------------------+ | start_time | timestamp(6) | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | user_host | mediumtext | NO | | NULL | | | query_time | time(6) | NO | | NULL | | | lock_time | time(6) | NO | | NULL | | | rows_sent | int(11) | NO | | NULL | | | rows_examined | int(11) | NO | | NULL | | | db | varchar(512) | NO | | NULL | | | last_insert_id | int(11) | NO | | NULL | | | insert_id | int(11) | NO | | NULL | | | server_id | int(10) unsigned | NO | | NULL | | | sql_text | mediumtext | NO | | NULL | | +----------------+------------------+------+-----+-------------------+-----------------------------+ 11 rows in set (0.02 sec) 也可以使用\G MariaDB [mysql]> desc slow_log\G; 1. row Field: start_time Type: timestamp(6) Null: NO Key: Default: CURRENT_TIMESTAMP Extra: on update CURRENT_TIMESTAMP 2. row Field: user_host Type: mediumtext Null: NO Key: Default: NULL Extra: 3. row Field: query_time Type: time(6) Null: NO Key: Default: NULL Extra: 4. row * 5，查看当前的用户 MariaDB [mysql]> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) MariaDB [mysql]> 6，查看当前所使用数据库 MariaDB [mysql]> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec) MariaDB [mysql]> 7，创建一个库 MariaDB [mysql]> create database linuxea; Query OK, 1 row affected (0.00 sec) MariaDB [mysql]> 8，创建一个表 MariaDB [mysql]> use linuxea; Database changed MariaDB [linuxea]> create table t1 (id int(4), name char(40)); Query OK, 0 rows affected (0.04 sec) MariaDB [linuxea]> 9，查看当前mysql状态 MariaDB [linuxea]> show status; +------------------------------------------+------------------+ | Variable_name | Value | +------------------------------------------+------------------+ | Aborted_clients | 0 | | Aborted_connects | 21 | | Access_denied_errors | 0 | | Aria_pagecache_blocks_not_flushed | 0 | | Aria_pagecache_blocks_unused | 15737 | | Aria_pagecache_blocks_used | 2 | | Aria_pagecache_read_requests | 70 | | Aria_pagecache_reads | 3 | | Aria_pagecache_write_requests | 6 | | Aria_pagecache_writes | 0 | | Aria_transaction_log_syncs | 0 | | Binlog_commits | 4 | | Binlog_group_commits | 4 | | Binlog_snapshot_file | mysql-bin.000002 | | Binlog_snapshot_position | 1805 | | Binlog_bytes_written | 201 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 4 | | Busy_time | 0.000000 | | Bytes_received | 416 | | Bytes_sent | 3161 | | Com_admin_commands | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | 10，查看mysql参数 MariaDB [linuxea]> show variables\G; 11，修改mysql参数 MariaDB [linuxea]> show variables like 'max_connect%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 10 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec) MariaDB [linuxea]> set global max_connect_errors = 1000; Query OK, 0 rows affected (0.00 sec) MariaDB [linuxea]> show variables like 'max_connect_errors'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 1000 | +--------------------+-------+ 1 row in set (0.00 sec) MariaDB [linuxea]> 12，查看当前mysql服务器的队列 MariaDB [linuxea]> show processlist; +----+------+-----------+---------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+---------+---------+------+-------+------------------+----------+ | 21 | root | localhost | NULL | Sleep | 1869 | | NULL | 0.000 | | 36 | root | localhost | linuxea | Query | 0 | NULL | show processlist | 0.000 | +----+------+-----------+---------+---------+------+-------+------------------+----------+ 2 rows in set (0.00 sec) 13，创建一个普通用户并授权 MariaDB [linuxea]> grant all on . to user1 identified by '123456'; Query OK, 0 rows affected (0.00 sec) all 表示所有的权限（读、写、查询、删除等等操作）， . 前面的 表示所有的数据库，后面的 表示所有的表，identified by 后面跟密码，用单引号括起来。这里的user1指的是localhost上的user1，如果是给网络上的其他机器上的某个用户授权则这样: MariaDB [linuxea]> grant all on linuxea. to 'linuxea'@'192.168.40.133' identified by '123'; Query OK, 0 rows affected (0.00 sec) MariaDB [linuxea]> 1，查询语句 MariaDB [linuxea]> select count() from mysql.user; +----------+ | count() | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) MariaDB [linuxea]> mysql.user表示mysql库的user表；count()表示表中共有多少行。 MariaDB [linuxea]> select from mysql.db; 这个用来表示查询mysql库的db表中的所有数据，也可以查询单个字段或者多个字段: MariaDB [linuxea]> select db from mysql.db; +---------+ | db | +---------+ | linuxea | | root | +---------+ 2 rows in set (0.00 sec) MariaDB [linuxea]> select db,user from mysql.db; +---------+---------+ | db | user | +---------+---------+ | linuxea | linuxea | | root | root | +---------+---------+ 2 rows in set (0.00 sec) MariaDB [linuxea]> 2. 插入一行 MariaDB [linuxea]> insert into linuxea.t1 values (1,'abc'); Query OK, 1 row affected (0.00 sec) MariaDB [linuxea]> select from linuxea.t1; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec) 3. 更改表的某一行 MariaDB [linuxea]> update linuxea.t1 set name='aaa' where id=1 ; Query OK, 0 rows affected (0.00 sec) MariaDB [linuxea]> select from linuxea.t1; 4，情况表数据 MariaDB [linuxea]> truncate table linuxea.t1; Query OK, 0 rows affected (0.02 sec) MariaDB [linuxea]> select count() from linuxea.t1; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) 5，删除表 MariaDB [linuxea]> drop table linuxea.t1; Query OK, 0 rows affected (0.00 sec) 6，删除数据库 MariaDB [linuxea]> drop database linuxea; Query OK, 0 rows affected (0.02 sec) 备份和恢复： buckup # mysqldump -uroot -p'passowrd' mysql
