mysql常用语句

本次mysql使用版本5.5

mysql 查用user和host

mysql> select user,host from mysql.user;
+------------+--------------------+
| user       | host               |
+------------+--------------------+
| programuse | 10.3.101.%         |
| root       | 127.0.0.1          |
| root       | ::1                |
|            | localhost          |
| chaxun     | localhost          |
| root       | localhost          |

mysql 授权用户

#只授权查询
mysql>grant select on *.* to rootabc@'%' identified by '123456'
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

#授权所有
grant all on *.*  to root@'127.0.0.1' identified by 'myFD23';

mysql 查看以及修改binlog 格式

#查看
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)
#设置 全局 binlog_format=ROW
mysql> set global binlog_format=ROW;
Query OK, 0 rows affected (0.00 sec)
# 退出再登录查询一下binlog_format
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

查看server_id 号

#查看
mysql>  show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)
#设置
mysql> set global server_id=220;
Query OK, 0 rows affected (0.00 sec)
# 确认一下
mysql>  show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 220   |
+---------------+-------+
1 row in set (0.00 sec)

查看bin log 有没有开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

查看binlog文件列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000281 | 211188101 |
| mysql-bin.000282 | 203583208 |
| mysql-bin.000283 | 235164712 |
| mysql-bin.000284 | 164658883 |
| mysql-bin.000285 | 180283549 |
| mysql-bin.000286 | 195744726 |
| mysql-bin.000287 | 188260161 |
| mysql-bin.000288 | 137658392 |
| mysql-bin.000289 | 216568100 |
| mysql-bin.000290 | 247486199 |
| mysql-bin.000291 | 223027216 |
| mysql-bin.000292 | 322807142 |
| mysql-bin.000293 | 344669787 |
| mysql-bin.000294 | 280084899 |
| mysql-bin.000295 | 252471375 |
| mysql-bin.000296 | 178860676 |
| mysql-bin.000297 | 212904888 |
| mysql-bin.000298 | 401105439 |
| mysql-bin.000299 | 263485384 |
| mysql-bin.000300 | 307953977 |
| mysql-bin.000301 | 253164634 |
| mysql-bin.000302 | 292608406 |
| mysql-bin.000303 | 230712354 |
| mysql-bin.000304 | 191188437 |
| mysql-bin.000305 | 202413422 |
| mysql-bin.000306 | 305030183 |
| mysql-bin.000307 | 287669720 |
| mysql-bin.000308 | 282238873 |
| mysql-bin.000309 | 443789621 |
| mysql-bin.000310 | 525860039 |
| mysql-bin.000311 | 150137139 |
| mysql-bin.000312 |   2229847 |
| mysql-bin.000313 |  27804281 |
+------------------+-----------+
33 rows in set (0.00 sec)

查看当前正在写入的binlog文件

mysql> show master status;   
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000313 | 28163019 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

查看指定binlog文件的内容

mysql>show binlog events in 'mysql-bin.000312';