I’ve compiled a listing of handy MySQL commands that I work really frequently inward a headless server. I am non a MySQL guru, in addition to then I decided to braindump everything inward here. This covers random issues from WordPress slowness, Query Cache Modification, User Creation, Database dumping in addition to importing, rsync’ing large files across servers in addition to many more. 
At the bottom are statements, clauses, in addition to functions you lot tin forcefulness out work inward MySQL. Below that are PHP in addition to Perl API functions you lot tin forcefulness out work to interface alongside MySQL. To work those you lot volition take away to construct PHP alongside MySQL functionality. To work MySQL alongside Perl you lot volition take away to work the Perl modules DBI in addition to DBD::mysql.
Below when you lot encounter # it way from the unix shell. When you lot encounter mysql> it way from a MySQL prompt later logging into MySQL.
So hither goes random mysql commands inward no item social club :)
Improving MySQL Query Cache
Important Note: From MySQL 5.6.8,query_cache_type is laid to OFF yesteryear default. So if you lot haven’t explicitly turned it ON on one-time version, it may non piece of work anymore!
Check electrical flow condition of query_cache
mysql -e "show variables similar 'query_cache_%'"
Will output something like:
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 268435456 |
| query_cache_strip_comments | ON |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
Changing Query Cache size:
mysql> SET GLOBAL query_cache_size = 16777216;
mysql> SHOW VARIABLES LIKE 'query_cache_size';
Output:
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| query_cache_size | 16777216 |
Query Cache Config
Add something similar below next to your /etc/my/my.cnf
query_cache_size =16777216
query_cache_type = 1
query_cache_limit = 2M
query_cache_strip_comments =1
Fixing ho-hum Query for the wp_options table
The argue the query is beingness logged is it doesn’t work an index. The query fourth dimension is 0, i.e. it truly executes fast. You tin forcefulness out unset the “log-queries-not-using-indexes” selection if you lot don’t desire these to live logged.
The wp_options tabular array has no index on autoload, in addition to then the query ends upwards doing a amount tabular array scan. In full general that tabular array shouldn’t larn also large, in addition to then it’s non a problem, simply I’m guessing that’s somehow happened inward your case.
Adding an index mightiness solve the problem, simply equally TheDeadMedic pointed out inward the comments, it mightiness non if the values of autoload are either bulk yes, or evenly distributed betwixt yeah in addition to no:
First, practise this query to encounter what the distribution looks like:
SELECT COUNT(*), autoload FROM wp_options GROUP BY autoload;
if a large bulk of them are laid to ‘no’, you lot tin forcefulness out solve the job for at i time yesteryear adding an index on autoload.
ALTER TABLE wp_options ADD INDEX (`autoload`);
Dumping i item MySQL Database
mysqldump -u username -p --databases database_name> /tmp/server1_database_name.sql
Importing i item MySQL Database
First practise user in addition to database
mysql -u source -p
create database database_name;
show databases;
use database_name;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'somepasswordhere';
GRANT ALL PRIVILEGES ON databse_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;
Move database dump from server 1 to server 2:
cd /tmp
rsync -avz --progress server1_username@server1_ip_address:/tmp/database_name.sql .
Dont forget the final . inward the to a higher house command.
Finally import that database you lot dumped inward server 1.
mysql -u username -p database_name < server1_database_name.sql
To login (from unix shell) work -h exclusively if needed.
# [mysql dir]/bin/mysql -h hostname -u source -p
Create a database on the sql server.
mysql> practise database [databasename];
List all databases on the sql server.
mysql> exhibit databases;
Switch to a database.
mysql> work [db name];
To encounter all the tables inward the db.
mysql> exhibit tables;
To encounter database’s plain formats.
mysql> pull [table name];
To delete a db.
mysql> drib database [database name];
To delete a table.
mysql> drib tabular array [table name];
Show all information inward a table.
mysql> SELECT * FROM [table name];
Returns the columns in addition to column information pertaining to the designated table.
mysql> exhibit columns from [table name];
Show for sure selected rows alongside the value “whatever”.
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the advert “Bob” AND the telephone number ‘3444444’.
mysql> SELECT * FROM [table name] WHERE advert = "Bob" AND phone_number = '3444444';
Show all records non containing the advert “Bob” AND the telephone number ‘3444444’ social club yesteryear the phone_number field.
mysql> SELECT * FROM [table name] WHERE advert != "Bob" AND phone_number = '3444444' social club yesteryear phone_number;
Show all records starting alongside the letters ‘bob’ AND the telephone number ‘3444444’.
mysql> SELECT * FROM [table name] WHERE advert similar "Bob%" AND phone_number = '3444444';
Show all records starting alongside the letters ‘bob’ AND the telephone number ‘3444444’ bound to records 1 through 5.
mysql> SELECT * FROM [table name] WHERE advert similar "Bob%" AND phone_number = '3444444' bound 1,5;
Use a regular seem to discovery records. Use “REGEXP BINARY” to forcefulness case-sensitivity. This finds whatever tape start alongside a.
mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";
Show unique records.
mysql> SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted inward an ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows.
mysql> SELECT COUNT(*) FROM [table name];
Sum column.
mysql> SELECT SUM(*) FROM [table name];
Join tables on mutual columns.
mysql> guide lookup.illustrationid, lookup.personid,person.birthday from lookup left bring together someone on lookup.personid=person.personid=statement to bring together birthday inward someone tabular array alongside main instance id;
Creating a novel user. Login equally root. Switch to the MySQL db. Make the user. Update privs.
# mysql -u source -p
mysql> work mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> even privileges;
Change a users password from unix shell.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'
Change a users password from MySQL prompt. Login equally root. Set the password. Update privs.
# mysql -u source -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> even privileges;
Recover a MySQL source password. Stop the MySQL server process. Start i time again alongside no grant tables. Login to MySQL equally root. Set novel password. Exit MySQL in addition to restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> work mysql;
mysql> update user laid password=PASSWORD("newrootpassword") where User='root';
mysql> even privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
Set a source password if at that topographic point is on source password.
# mysqladmin -u source password newpassword
Update a source password.
# mysqladmin -u source -p oldpassword newpassword
Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login equally root. Switch to the MySQL db. Give privs. Update privs.
# mysql -u source -p
mysql> work mysql;
mysql> grant usage on *.* to bob@localhost identified yesteryear 'passwd';
mysql> even privileges;
Give user privilages for a db. Login equally root. Switch to the MySQL db. Grant privs. Update privs.
# mysql -u source -p
mysql> work mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> even privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> even privileges;
To update information already inward a table.
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
Delete a row(s) from a table.
mysql> DELETE from [table name] where [field name] = 'whatever';
Update database permissions/privilages.
mysql> even privileges;
Delete a column.
mysql> alter tabular array [table name] drib column [column name];
Add a novel column to db.
mysql> alter tabular array [table name] add together column [new column name] varchar (20);
Change column name.
mysql> alter tabular array [table name] alter [old column name] [new column name] varchar (50);
Make a unique column in addition to then you lot larn no dupes.
mysql> alter tabular array [table name] add together unique ([column name]);
Make a column bigger.
mysql> alter tabular array [table name] modify [column name] VARCHAR(3);
Delete unique from table.
mysql> alter tabular array [table name] drib index [colmn name];
Load a CSV file into a table.
mysql> LOAD DATA INFILE '/tmp/filename.csv' supervene upon INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Dump all databases for backup. Backup file is sql commands to recreate all db’s.
# [mysql dir]/bin/mysqldump -u source -ppassword --opt >/tmp/alldatabases.sql
Dump i database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a tabular array from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1.
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2.
mysql> practise tabular array [table name] (personid int(50) non zero auto_increment main key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
Sources:
- https://mariadb.com/kb/en/mariadb/sql-commands/
- https://www.pantz.org/software/mysql/mysqlcommands.html
- http://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html
- https://easyengine.io/tutorials/mysql/query-cache/
- http://wordpress.stackexchange.com/questions/71691/slow-query-for-the-wp-options-table
