MySQL Common operations

/usr/local/sbin/apachectl Graceful

Create user ' test ' @ ' 192.168.1.1 ' identified by ' 111111 ';

Grant all privileges on * * to test@ ' localhost ' identified by ' 111111 '; Natively

Grant all privileges on *. * to test@ '% ' identified by ' 111111 '; % allows network-side hosts to access the database

Delete User name delete from user WHERE user= "Jude" and host= "localhost";

mysql> UPDATE user SET password= Password (' 111111 ') WHERE user= ' MySQL ';

FLUSH privileges;

MySQL Common maintenance commands

Show Processlist

If the root account, you can see all users of the current connection, if it is a normal account, can only see their own occupied connection

Show Processlist can only list the first 100; if you want to list them all, use show full processlist

1. Show global status; list MySQL server running various status values

2, show variables; querying MySQL server configuration information statements

3. View Slow Query

Show variables like '%slow% ';

Show global status like '%slow% ';

4, the maximum number of connections

Show variables like ' max_connections '; Maximum number of MySQL server connections

Show global status like ' Max_used_connections '; Maximum number of connections for server response

5. View table structure

Desc Tablename;

Describe Tablename;

Show columns from Tablename;

Show CREATE TABLE Tablename

Show Binlog Enevts in ' master-bin0000001 '; Production look Binlog log execution content

Show global variables like ' read% ';

vi/etc/my.conf read-only = On set database sub-read mode

Master_info Real_info

Sync-binlog =on Setting user transaction security on the primary server

Percona-tools percona-toolkit view MySQL master-slave synchronization Information tool

Pt-slave-delay-h

You can also view the system hardware load

MySQL monitor is commonly used by several MySQL commands.

Status = Show status like '% ' [example: Show status like ' Com_select ']
variables = Show variables like '% ' [example: show variables like ' query_cache_size ']

1. Number of MySQL queries (status)
Com_select; Com_update; Com_insert; Com_delete; com_change_db

2, query cache space size: query_cache_size (variables)
Query cache maximum query data set size: Query_cache_limit (variables);
Number of queries in cache: Qcache_inserts (status);
Query Cache Hit Ratio: (qcache_hits/(Qcache_hits+qcache_inserts)) *100% (status)

3. Index Cache Hit Ratio
Index cache space Size: key_buffer_size (variables)
Index cache Hit Ratio: (key_reads/key_read_requests) *100% (status)

4. Number of concurrent connections
Maximum number of connections: max_connections (variables)
Actual maximum number of connections: max_used_connections (status)
Current number of connections: threads_connected (status)
Number of active connections: threads_running (status)
Number of Cache connections: Threads_cache (status)

5, Traffic statistics (status)
Bytes_received, bytes_sent (status)

6. Number of connections
Number of connections per second: Connections (status)
Number of actual connections created per second: threads_created (status)

7. Table Lock Statistics
Number of table locks released immediately: table_locks_immediate (status)
Number of table locks to wait: table_locks_waited (status)

Database backup Scripts

#!/bin/bash
Date= ' Date +%y%m%d%h%m '
Database=test
Backup=/data/backup
Password= "111111"
/usr/local/mysql/bin/mysqldump-umysql-h 127.0.0.1-p$password-r--opt $DATABASE |gzip > ${backup}\/${database}_${ Date}.sql.gz Sleep 3
Find $BACKUP-mtime +10 |xargs RM-RF

This article is from the "Linux Learning:" blog, be sure to keep this source http://haozi4263.blog.51cto.com/2791641/1663668

MySQL Common operations