mysql 常用

原创 2017年12月01日 10:56:18

基本信息


mysql> show variables where Variable_name in ('log_error','general_log','general_log_file','slow_query_log','slow_query_log_file','datadir','basedir','innodb_buffer_pool_size','performance_schema','version','character_set_database');
+-------------------------+------------------------------------------------------------------+
| Variable_name           | Value                                                            |
+-------------------------+------------------------------------------------------------------+
| basedir                 | /usr/                                                            |
| character_set_database  | utf8mb4                                                          |
| datadir                 | /var/lib/mysql/data/                                             |
| general_log             | OFF                                                              |
| general_log_file        | /var/lib/mysql/data/RDS-9A434B86-5696-923D-AF2E-2A9A1450B86F.log |
| innodb_buffer_pool_size | 9814671360                                                       |
| log_error               | /var/log/mysqld.log                                              |
| performance_schema      | ON                                                               |
| slow_query_log          | ON                                                               |
| slow_query_log_file     | /var/lib/mysql/data/mysql-slow.log                               |
| version                 | 5.6.35-log                                                       |
+-------------------------+------------------------------------------------------------------+
11 rows in set (0.01 sec)

//数据库
mysql> SELECT TABLE_SCHEMA,concat(round((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024,2),'MB') as size FROM information_schema.TABLES group by TABLE_SCHEMA;
+--------------------+------------+
| TABLE_SCHEMA       | size       |
+--------------------+------------+
| agiledb            | 96948.18MB |
| information_schema | 0.01MB     |
| mysql              | 2.49MB     |
| performance_schema | 0.00MB     |
+--------------------+------------+
4 rows in set, 1 warning (0.05 sec)

创建库和授权

mysql> create database mycat_eye;  
Query OK, 1 row affected (0.01 sec) --创建mycat_eye库   
  
mysql> GRANT all ON mycat_eye.* TO root@'%' IDENTIFIED BY '123456';  
Query OK, 0 rows affected (0.03 sec)--给dss用户对mycat_eye库授权  
  
mysql> flush privileges;  
Query OK, 0 rows affected (0.02 sec)--刷新系统权限表  

显示表记录
mysql> select table_name,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2),'MB') as size,table_rows from information_schema.tables where table_schema="mycat_
eye" order by table_rows desc limit 10;
+----------------------+---------+------------+
| table_name           | size    | table_rows |
+----------------------+---------+------------+
| mysql_status_history | 18.06MB |      45362 |
| mysql_server         | 0.03MB  |          3 |
| mysql_cluster        | 0.02MB  |          1 |
| sys_user             | 0.05MB  |          1 |
| mysql_weak_password  | 0.03MB  |          1 |
+----------------------+---------+------------+
5 rows in set (0.00 sec)

二进制

mysql> SHOW BINARY LOGS;
+-----------+-----------+
| Log_name  | File_size |
+-----------+-----------+
| ON.000001 |  87199642 |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> SHOW BINLOG EVENTS IN 'ON.000001' LIMIT 10, 8;
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------+
| Log_name  | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                           |
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------+
| ON.000001 | 1286 | Anonymous_Gtid |      1122 |        1351 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                           |
| ON.000001 | 1351 | Query          |      1122 |        1428 | BEGIN                                                                          |
| ON.000001 | 1428 | Table_map      |      1122 |        1494 | table_id: 108 (mycat_eye.mysql_cluster)                                        |
| ON.000001 | 1494 | Write_rows     |      1122 |        1556 | table_id: 108 flags: STMT_END_F                                                |
| ON.000001 | 1556 | Xid            |      1122 |        1587 | COMMIT /* xid=45 */                                                            |
| ON.000001 | 1587 | Anonymous_Gtid |      1122 |        1652 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                           |
| ON.000001 | 1652 | Query          |      1122 |        1797 | use `mycat_eye`; DROP TABLE IF EXISTS `mysql_server` /* generated by server */ |
| ON.000001 | 1797 | Anonymous_Gtid |      1122 |        1862 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                           |
+-----------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

主Master信息

mysql> show master status;
+-----------+----------+--------------+------------------+-------------------+
| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+----------+--------------+------------------+-------------------+
| ON.000001 | 87581468 |              |                  |                   |
+-----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|      1127 |      | 3307 |      1122 | ec83bdff-d4d1-11e7-a203-00ff3dca5ad5 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

从slave信息

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: ON.000001
          Read_Master_Log_Pos: 87963280
               Relay_Log_File: AHQ-PC-01057-relay-bin.000005
                Relay_Log_Pos: 6680878
        Relay_Master_Log_File: ON.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 87963280
              Relay_Log_Space: 87963503
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1122
                  Master_UUID: ec83bdff-d4d1-11e7-a203-00ff3dca5ad4
             Master_Info_File: D:\Mysql\MySQL5.72\data\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

主从配置
1、主从服务器分别作以下操作:
   版本一致
2、修改主服务器master:
   linux  my.cnf
   win  my-default.ini
    [mysqld]
    log_bin         = ON   //[必须]启用二进制日志
    binlog_format   = ROW
    server_id = 1122 //[必须]服务器唯一ID
3、修改从服务器slave:
   #vi /etc/my.cnf
   [mysqld]
   log_bin  = ON   //[不是必须]启用二进制日志
   server_id = 1123 //[必须]服务器唯一ID
   执行 
   stop slave;
   change master to master_host='127.0.0.1',master_user='root',master_password='123456',master_log_file='ON.000001',master_log_pos=463;
   start slave;


解决 Slave_SQL_Running: No
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;


#免密码登录
my-default.ini的[mysqld]新增
#免密码登录
skip-grant-tables
#修改管理员密码
#update user set password=PASSWORD("123456") where user='root';


半同步
  在半同步复制中, 为了保证主库上的每一个binlog事务都能够被可靠的复制到从库上,主库在每次事务成功提交时, 并不及时反馈给前端应用用户,而是等待其中之一个从库也接收到Binlog事务并成功写入中继日志后, 主库才返回Commit操作成功给客户端。如果同步出现故障, 则MySQL自动调整复制为异步模式,事务正常返回提交结果给客户端。半同步服务需要安装插件.


1. 首先判断MySQL服务器是否支持动态增加插件
mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.00 sec)


2 安装插件
#查看插件存入地址
mysql> show variables like 'plugin_dir';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| plugin_dir    | D:\mysql\mysql-5.6.25-winx64\lib\plugin\ |
+---------------+------------------------------------------+
1 row in set (0.00 sec)


#Master安装 semisync_master.dll(linux安装semisync_master.so)
mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.dll';
Query OK, 0 rows affected (0.07 sec)


#Slave安装(linux安装semisync_slave.so)
mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.dll';
Query OK, 0 rows affected (0.49 sec)


#查看Master
mysql> select * from mysql.plugin;
+----------------------+---------------------+
| name                 | dl                  |
+----------------------+---------------------+
| rpl_semi_sync_master | semisync_master.dll |
+----------------------+---------------------+
1 row in set (0.00 sec)


#查看Slave
mysql> select * from mysql.plugin;
+---------------------+--------------------+
| name                | dl                 |
+---------------------+--------------------+
| rpl_semi_sync_slave | semisync_slave.dll |
+---------------------+--------------------+
1 row in set (0.10 sec)


3.分别配置主从数据库my-default.ini, 打开半同步semi-sync
Master(linux my.cnf)
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=30000
Slave(my.cnf)
rpl_semi_sync_slave_enabled=1


4.检查
Master
mysql> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)


mysql> show variables like '%Rpl%';
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| rpl_semi_sync_master_enabled       | ON       |
| rpl_semi_sync_master_timeout       | 30000    |
| rpl_semi_sync_master_trace_level   | 32       |
| rpl_semi_sync_master_wait_no_slave | ON       |
| rpl_stop_slave_timeout             | 31536000 |
+------------------------------------+----------+
5 rows in set (0.00 sec)


Slave
mysql> show status like '%semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.02 sec)


mysql> show variables like '%Rpl%';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled     | ON       |
| rpl_semi_sync_slave_trace_level | 32       |
| rpl_stop_slave_timeout          | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)

MYSQL常用命令

  • 2015年12月22日 16:44
  • 15KB
  • 下载

JavaWeb常用开发技术总结-js、spring、javabean、mysql数据库、dwr的使用等

JaveWeb 程序开发 一、    表单及表单元素 a)      文本框的应用: 不能复制,剪贴,黏贴一般用于密码框! 二、    窗口与导航条 a)       打开一个窗口自动关...

MySql常用命令总结

  • 2014年05月20日 10:24
  • 30KB
  • 下载

mysql常用基础

  • 2017年05月30日 16:50
  • 199KB
  • 下载

MySQL 入门常用命令大全(下)

3.4DQL 篇(数据查询篇) 3.4.1 查询记录 #命令格式 mysql> SELECT [ 列名称] FROM [ 表名称] where [ 条件] 说明:一个完整的 SELEC...

mysql一些常用命令

  • 2017年06月23日 14:33
  • 2KB
  • 下载

Mysql数据库常用分库和分表方式

1 分库 1.1 按照功能分库     按照功能进行分库。常见的分成6大库:     1 用户类库:用于保存了用户的相关信息。例如:db_user,db_system,db_company等。    ...

MySql常用的语句

  • 2010年08月11日 18:58
  • 8KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql 常用
举报原因:
原因补充:

(最多只允许输入30个字)