VPSGet - Knowledgebase

Mysql

Installing mysql:
yum install mysql-server
Enter to mysql:
mysql
-u user
-p password
User root is used by default

Quit mysql:
\q
Show status
\s
Set root password:
mysqladmin -u root password <password>  #not from the inside of mysql CLI
Enter in mysql CLI
mysql -u root -p
Create database (simple):
CREATE DATABASE <DBname>;
Create database "db1" with charset UTF8:
mysql> CREATE DATABASE `db1` CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.02 sec)
Set current database:
USE <DBname>;
Create user "ted" with password "secret" allowed to connect only from localhost with full access:
mysql> CREATE USER 'ted'@'localhost' IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ted'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
Create user "ted" with password "secret" allowed to connect from any host with full access:
mysql> CREATE USER 'ted'@'%' IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ted'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
Show users: mysql> SELECT HOST, USER, PASSWORD FROM mysql.user;
+-------------------+------+-------------------------------------------+
| HOST              | USER | PASSWORD                                  |
+-------------------+------+-------------------------------------------+
| localhost         | root | *2502D313BE1E9A6E3B593D54E1949B392B899448 |
| WordPress\_kvm106 | root |                                           |
| 127.0.0.1         | root |                                           |
| localhost         |      |                                           |
| WordPress\_kvm106 |      |                                           |
| localhost         | ted  | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 |
| %                 | ted  | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 |
+-------------------+------+-------------------------------------------+
7 rows in set (0.00 sec)
So, two accounts with username "ted" and password "secret" have been created. Both are superusers with all rights. One for use to connect from localhost and another to connect from anywhere.
It is necessary to have both accounts for user "ted" to be able to connect from anywhere as "ted".

Allow user "ted" to access the database "db1":
mysql> GRANT ALL PRIVILEGES ON db1.* TO 'ted'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
Show databases:
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec
Mysql some examles:

Delete test database
DROP DATABASE test;
Show all databases
SHOW DATABASES;
show mysql users
SELECT User FROM mysql.user;
use database
use <dbname>;
show selected db tables:
show tables;
show columns for specified table
show columns from <tableName>
select all users from somedbtable (in this case we are already have selected db):
select User from tblusers
select all users from some db table w/o select db first:
SELECT * from <dbname>.<tablename> where user_name = 'testuser';
select user with specified name:
SELECT * from <dbname> where user_name ='testuser'; 
select users except specified:
SELECT * from <dbname> where user_name <> 'testuser';
select with AND
SELECT * from <dbname> where user_name = 'testuser1' or user_name = 'testuser2';
select by ID range:
SELECT * from <dbname> where (id>10 and id<40);
Update few user fields fro all users except testuser1 and testuser2
 UPDATE <dbname>.<tablename>.user SET
 user_password    = 'somevalue',
 user_newpassword = 'somevalue',
 user_email       = 'somevalue',
 user_token       = 'somevalue'
 WHERE
 user_name <> 'testuser1' and user_name <> 'testuser2';
Update change some field:
UPDATE <tablename>  SET <field> = '<value>'  WHERE [criteria]; 
Example:
UPDATE vservers  SET mainipaddress = '10.10.10.14'  WHERE ctid=5342;
Create table records:
INSERT INTO <table_name> VALUES (758,5,'some_string','some_string',0);
Delete some table record:
DELETE FROM <table_name> WHERE (id=35);
Backup database
mysqldump -u user -p password db1 > db1.sql
Backup all databases
mysqldump -u root -p --all-databases > alldb.sql
#or
mysqldump --all-databases > alldb.sql 
Restore database
mysqldump -u user -p password db1 < db1.sql
In some cases you should not specify the credentials"
mysqldump db1 > db1.sql #backup
mysqldump db1 < db1.sql #restore
In newer verison of mysql you need to use simply "mysql" comand instead "mysqldump". like:
mysql db1 < db1.sql
Resolving issues

if you cant run mysqld under openvz container and face next error in logs:
mysqld: 120626 10:20:26 InnoDB: Using Linux native AIO
mysqld: 120626 10:20:26 InnoDB: Warning: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
mysqld: InnoDB: Warning: io_setup() attempt 1 failed.
you have 2 ways to fix it:

1. Disable AIO in all containers' my.cnf files by adding
innodb_use_native_aio=0
2. Increase the global limit for the server in
/proc/sys/fs/aio-max-nr
Another case when you cant start mysql service with next error:
-- Startup Output --
Starting MySQL... ERROR! The server quit without updating PID file (/var/lib/mysql/mysqlserver.pid)
040113 01:52:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
040113  1:52:41 [Note] Plugin 'FEDERATED' is disabled.
040113  1:52:42 InnoDB: The InnoDB memory heap is disabled
040113  1:52:42 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
040113  1:52:42 InnoDB: Compressed tables use zlib 1.2.3
040113  1:52:42 InnoDB: Initializing buffer pool, size = 128.0M
040113  1:52:42 InnoDB: Completed initialization of buffer pool
040113  1:52:42  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ib_logfile0
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
040113 01:52:42 mysqld_safe mysqld from pid file /var/lib/mysql/mysqlserver.pid ended
-- End Startup Output --
Operating system error number 13 means "Permission denied" You can notify it using perror command:
#perror 13
To fix this issue you need to fix the permissions:
#chown -R mysql:mysql /var/lib/mysql
If you're experiencing a performance issues like high CPU load, you can install and run "mysqltuner" utility which is telling you what is wrong and how to make it right.
yum install mysqltuner
mysqltuner
After you can start mysql service.

Reset Mysql root password:
#/etc/init.d/mysql stop
#mysqld_safe --skip-grant-tables &
#mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
#/etc/init.d/mysql stop
#/etc/init.d/mysql start
#mysql -u root -p