Common MySQL Commands

View existing databases show databases;
Create the database and view the CREATE database name;
Delete the database name of the drop databases database;
Use the specified database using the database name;
View tables in the database show tables;
CREATE table tables table name;
View table structure describe table name;
Delete table name of the drop tables table;
Database backup mysqldump--opt database name > storage location/file name
Recovery of databases drop database db1;->careate database db1;->mysql db1</tmp/db1.bak;-> Test use db1-> Select ...
Sets the password set Password=password (' 123 ');
Delete the anonymous user delete from user where user= ';
Update authorization form flush privileges;
Standard syntax for user authorization:
Grant permissions on database/table to user name identified by ' Password ' with ' Special permissions '

such as: Grant all on * * to [email protected] identified by ' 123 ' with GRANT option;
Authorization does not qualify host grant all on * * to [email protected] '% ' identified by ' 123 ' with GRANT option;
Authorized user to specify IP login with full permissions on all databases

Grant all on *. * [email protected]%.% identified by ' 123 ';

Test User Dbau2
Mysql-u Dbau2-p 123

Revoke user Rights standard syntax
Revoke Insert,delete on * * FROM [email protected];
Revoke all permissions for a specified user
Revoke all on * * from [email protected] '% ';

View Packages
Rpm-qa | grep MySQL
Install packages
Rpm-ivh
Start | stop | restart MySQL service
Service Mysqld Start|stop|restart
/etc/init.d/mysqld Start|stop|restart
Secure background to start the MySQL daemon
safe_mysqld&
View the MySQL daemon
Service MSYQLD Status
Turn off MySQL
Mysqladmin shutdown
Log in to MySQL without a username password
Specify root User Login
Mysql-u Root
Log in after entering the root password
Mysql-u root-p

Duplicate table structure
CREATE TABLE new_table (ID int () NOT NULL auto_increment PRIMARY key) SELECT * FROM Old_table

TRUNCATE TABLE ' 666 '

Mysql-u root-p
Mysqldump-u root-p omsadmin > word.sql; Exporting tables
Mysql-u root-p Ucms_2_0 < hubei.txt execute a command

Export a specific table structure and data
Mysqldump-uroot-p omsadmin evaluating_data >/home/yuejd/evaluating_data.sql;

If export is empty, you can add host
Mysqldump-h 192.168.42.129-u root-p vip bt_config >/apps/sql/bt_config.sql;

Export a specific table structure
Mysqldump-uroot-p-D omsadmin evaluating_data>/home/yuejd/evaluating_data.sql;

Build Index
ALTER TABLE ' log_analysis '. ' wap_query_pv_week_2011_28 ' DROP PRIMARY KEY,
ADD PRIMARY KEY (' id ');
ALTER TABLE ' wap_query_pv_week_2011_28 ' ADD INDEX (' query ');

ALTER TABLE ' offical ' ADD ' ww ' INT (+) not NULL after ' Link_wap '
==================================================================================
Start MySQL:
/usr/mysql/bin/mysqld_safe--user=mysql &
MySQL Authorization:
Grant all on * * to [e-mail protected] identified by "456";
To view the authorization form:
Select User,host,password from Mysql.user
To refresh the log:
Flush logs;
To view the last log:
Show master status;
To clear the Binlog log:
Reset Master;
To view the binary Binlog log:
/USR/LOCAL/MYSQL/BIN/MYSQLBINLOG/USR/LOCAL/MYSQL/LOGS/MYSQL-BIN.000001 (followed by absolute path)
/usr/local/mysql/bin/mysqlbinlog--no-defaults/usr/local/mysql/logs/mysql-bin.000001 (ignoring coding issues)

Socket Login
Mysql-uroot-p-s/usr/local/mysql/3307/mysql.sock--port=3307
Backup:
/usr/local/mysql/bin/mysqldump-uroot-p1qazxsw2-s/usr/local/mysql/3307/mysql.sock--port=3307 testbin-l-f >/usr /local/temp/test.sql
Recovery:
/usr/local/mysql/bin/mysql-uroot-p1qazxsw2-s/usr/local/mysql/3307/mysql.sock--port=3307 Testbin </usr/local/ Temp/test.sql
Recover Binlog logs:
/usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/3307/logs/binlog/mysql-bin.000020 |/usr/local/mysql/bin/mysql- Uroot-p1qazxsw2-s/usr/local/mysql/3307/mysql.sock--port=3307 testbin (database)
Mysqlbinlog followed by the main parameters:
--start-position= "200"
--stop-position= "500"
--start-date= "2012-03-29 21:20:20"
--stop-date= "2012-03-29 22:00:00"
Restore by Location:
/usr/local/mysql/bin/mysqlbinlog--stop-position= "1501"/usr/local/mysql/3307/logs/binlog/mysql-bin.000021 |/usr/ Local/mysql/bin/mysql-uroot-p1qazxsw2-s/usr/local/mysql/3307/mysql.sock--port=3307 testbin (log in behind the database)
MySQL master-Slave synchronization:
Configuration from service:
/ETC/MY.CNF's Slave
server-id=2
master-host=192.168.10.1
Master-user=user
Master-password=pass
master-port=3306
Log-bin=mysql-bin
#replicate-do-db=test
#replicate-do-table=test.t1
Reboot:
Service mysqld Restart
View:
Mysql>show slave status\g;
Slave_io_running:yes
Slave_sql_running:yes
Causing the synchronization to fail:
Slave_sql_running:no
Cause: A, the program may have a write operation on slave B, or it could be a transaction rollback after slave restart
Workaround:
(1)
Mysql>slave stop;
Mysql>set GLOBAL sql_slave_skip_counter=1;
Mysql>slave start;
(2)
View Binlog and then manually synchronize the Binlog log
Mysql>slave stop;
Mysql>show Master status;
Mysql>change Master to
master-host=192.168.10.1,
Master-user=user,
Master-password=pass,
master-port=3306,
Master_log_file= "mysql-bin.000003",
master_log_pos=98;
Mysql>slave start;
MySQL Partition:
Partitioning advantages Disadvantages Common
Rang partitioning is suitable for date types and supports composite partitioning limited partitioning is typically only for a single column

The list partition is suitable for columns with fixed values, supports composite partitioning with limited partitioning, and inserts values
Not in list, data loss is generally only for a column

Hash partition linear Hash makes the data distribution of adding, deleting and partitioning linear hashes uneven,
Faster general hash more uniform generally only for a certain column
The key partitioning column is less efficient than other non-int types such as character type, because there are multiple
Miscellaneous function Calculations are generally only for a column
View the engines used
Show engines;
See if partitioning is supported
Show plugins;
Example by hash:
CREATE TABLE T2 (
ID int)
Engine=myisam
Partition by hash (ID)
Partitions 5;
Stored Procedure plus data
mysql>\d//Change delimiter
Mysql>create Procedure P3 ()
->begin
->set @i=1;
->while @i<10000 Do
->insert into T3 values (@i);
->set @[email protected]+1;
->end while;
->end//

Execute storage: Call P3 ();

InnoDB do partition must be set
innodb_file_per_table = 1
To do the partitioning. Otherwise the index and data are present ibdata1 inside.
Because InnoDB share tablespaces and stand-alone table spaces

MySQL Basic operation:
MySQL table replication
Structure table replication: CREATE table t3 like T1;
Data table replication: INSERT INTO t3 select * from T1;
One statement data result and data: CREATE TABLE user1 as SELECT * from ' user '
Only copy data: CREATE TABLE user1 as SELECT * from ' user ' WHERE 1=2
Take a random number
SELECT * FROM fetionsquarecontent WHERE ID < 420 ORDER by RAND () LIMIT 5;
Use rollup to count more information and not to use it with order by
SELECT Groupid,count (GroupID) as CNT from Fg_grouptopic GROUP by GroupID with rollup

Set word defaults think null this will use the index
The fields after and and or before are indexed, otherwise the index is invalid
The string index is set, and if you are searching for an integer type, it is best to enclose it in double quotation marks, otherwise you cannot use the index

Optimize: Optimizing table space, fragmentation
Optimize table T4;

Import and export a column of a database
Export: Select name from T4 to OutFile "/tmp/test.txt";
Import: Load Data infile "/tmp/test.txt" into table t4 (name);
Close Normal index before import: ALTER TABLE t1 disable keys;
Open Normal index after import: ALTER TABLE T1 enable keys;
Close Unique index: set unique_checks=0;
Open Unique index: set Unique_checks=1;

Turn off grouping sorting to improve performance
SELECT * FROM T2 Group BY class order by null
Read lock, can only read can not do other operations
Lock table T1 read;
Unlock: Unlock tables;
Write Lock: Other users can not read and modify the delete operation
Lock table T1 Write

Log:
Vi/etc/my.cnf
Log-bin=mysql-bin
Log_slow_queries=slow.log
Forgot password How to modify
Service Mysqld Stop
Skip Login
/user/local/mysql/mysqld_safe--skip-grant-tables--user=mysql &

==================2014======================
Authorizing user users to log on from the server password is 456
Grant all on * * to [e-mail protected] identified by "456";

Log in to 10.1 from the 192.168.10.2 server
/usr/local/mysql/bin/mysql-uuser-p456-h192.168.10.1
===================mysql Partition ========================

//rang partition, based on the range partition of the ID
CREATE TABLE ' exployee '. ' Exployeerange ' (
' id ' INT () UNSIGNED not NULL auto_increment,< br> ' fname ' varchar (+) CHARACTER set UTF8 COLLATE utf8_unicode_ci not NULL,
' lname ' varchar (CHARACTER set UTF8) COLLATE utf8_unicode_ci NOT NULL,
' hired ' date is not null,
' separated ' date is not null,
' Job_code ' INT (+) not NULL,
' store_id ' INT (Ten) not NULL,
PRIMARY KEY (id,store_id)
) ENGINE = InnoDB PARTITION by RANGE (store_id) (partition P0 values less than (6),
PARTITION P1
Values less than (one),
PARTITION P2
Values less than (16 ),
PARTITION P3
VALUES less than (+)
);

//list partition, based on zone partition
CREATE TABLE ' exployee '. ' Exployeelist ' (
' id ' INT () UNSIGNED not NULL auto_increment,
' fname ' varchar (CHARACTER) Set UTF8 COLLATE utf8_unicode_ci not NULL,
' lname ' varchar (CHARACTER set UTF8 COL Late utf8_unicode_ci NOT NULL,
' hired ' date is not null,
' separated ' date is not null,
' Job_code ' INT (+) NOT NULL ,
' store_id ' INT (Ten) is not NULL,
PRIMARY KEY (id,store_id)
) ENGINE = MyISAM PARTITION by LIST (store_id) (
PAR Tition Pnorth values in (3,5,6,9,17),
PARTITION peast values in (1,2,10,11,19,20),
PARTITION pwest values in (4, 12,13,14,18),
PARTITION pcenter VALUES in (7,8,15,16)
);

Hash partition, based on year partition
CREATE TABLE ' exployee '. ' Exployeehash ' (
' ID ' INT (Ten) UNSIGNED not NULL auto_increment,
' FName ' VARCHAR (+) CHARACTER SET UTF8 COLLATE utf8_unicode_ci not NULL,
' LName ' VARCHAR (+) CHARACTER SET UTF8 COLLATE utf8_unicode_ci not NULL,
' Hired ' DATE not NULL,
' Separated ' DATE not NULL,
' Job_code ' INT (a) is not NULL,
' store_id ' INT (Ten) is not NULL,
PRIMARY KEY (id,hired)
) ENGINE = MyISAM PARTITION by HASH (year (hired)) (
Partitions 4
);

Common MySQL Commands