mysql basic

Although phpmyadmin can release you from remembering most mysql instructions, sometimes you have to use a few mysql commands. For example, on my NAT VPS, I do not install phpmyadmin due to limited disk space. Other circumstances that you have to resort to mysql commands include exporting/importing large databases.

 

Warning: If you use innodb database, be sure to add innodb_file_per_table in /etc/my.cnf not to regret in the end! By default, mysql puts the data of all innodb databases into a single file:/var/lib/mysql/ibdata1. This file grows larger and larger every day, until one day it consumes all the disk space. Then some bad things happen. First, your mysqld restarts repeatedly and cannot be stopped. Checking /var/log/mysql.log, you will find the error saying no disk space to create file,etc. Issuing df command, you will see the disk space is 100% used up. Unless you can find some files to delete, you cannot stop mysqld crash and restart endlessly. Until you delete some files to release some disk space, you can let mysqld run normally. Now, you must be  eager to clean some of your databases that occupy too much disk space. But soon you will find it is an impossible mission. Deleting some rows from tables won’t let ibdata1 shrink. Dropping tables won’t, either. Erasing the whole database does not reduce the size of ibdata1,either. The only thing left for you seems to delete the ibdata1 that eats up the disk space. You are certainly hoping to backup the database before doing so. Unfortunately, since there is no space on the disk,it is most likely that you cannot use the mysqldump command to export the database.   You may have some illusion that there must be a way to  compress the database  to release some disk space. However, without innodb_file_per_table being enabled, database compression is impossible. The optimize command also loses its optimization functionality  at this critical time because it uses extra space to copy tables. At this time, you have two choices: delete the ibdata1 file anyway losing all the precious data. Things may get even worse if you use sentora or other control panel for server management as you will lose all the system data that the control panel program uses.  In the extreme case, you will have to re-install the OS, re-install control panel on your server and setup all the things such as setting up websites,etc. all over again. The other choice is upgrading your server plan to buy more disk space to fix the database problem(but this option is not always possible for resource exhausted servers), or buying another vps with more disk space and transfer database files to it for exporting/repairing. Only at this time will you be aware of the relationship between mysql and hosting server providers, which is like the ally between M$ and hardware vendors, or between G$ and domain registrars.

FAQs

How to check the mysql version?

mysql -V

This command does not require mysqld is running.

How to know the config file mysqld loads?

mysql –help | grep Default -A 1
It will show:

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

How to know what databases are in the current system?

show databases;

How to create a database?

create database databasename default character set utf8 collate utf8_general_ci;

Don’t use “create database databasename” as that will create a database with the default character set latin1 and default collate latin1_swedish_ci, which may bring you a lot of trouble later.

How to delete a database?

drop databasename;

How to know the default storage engine?

SHOW GLOBAL VARIABLES LIKE ‘storage_engine’; (refer to this post)

How to set the default storage engine?

modify /etc/my.cnf and add the following line to the [mysqld] section, then restart mysqld:

default-storage-engine=InnoDB (refer to this post)

How to know what tables use innodb storage engine in a database?

SELECT table_name FROM information_schema.tables WHERE engine=’InnoDB’ and table_schema=’databasename’; ]

How to know the storage engine of a table?

select engine from information_schema.tables where table_schema=’databasename’ and table_name=’tablename’;

You can also use the following command to check the storage engine for a table:

show table status where name=’tablename’;

Do MyISAM tables use ibdata1?

No. MyISAM tables use tablename.MYD and tablename.MYI to store data and index, use tablename.frm to store schema. They do not use /var/lib/mysql/ibdata1. So you can safely transfer the three files to another machine to rebuild the database.

 

How to show tables in a database?

use databasename;

show tables;

Or,

show tables in databasename;

Or,

show tables from databasename;

Or,

show table status from databasename;

How to add a user?

CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;

How to grant privileges to user?

GRANT ALL PRIVILEGES ON database.table TO ‘newuser’@’localhost’;

To grant privileges on all tables of a database to user:

GRANT ALL PRIVILEGES ON database.* TO ‘newuser’@’localhost’;

How to delete a user?

DROP USER ‘demo’@‘localhost’;

How to reset the password if I forget it?

service mysqld stop

/usr/sbin/mysqld –skip-grant-tables –skip-networking &

mysql -u root

FLUSH PRIVILEGES;

SET PASSWORD FOR root@’localhost’ = PASSWORD(‘password’);

UPDATE mysql.user SET Password=PASSWORD(‘newpwd’) WHERE User=’root’;

service mysqld restart

How to set a password for the root?

use mysql

UPDATE USER SET Password=PASSWORD(‘new-password-here’) WHERE USER=’root’ AND Host=’localhost’;

FLUSH PRIVILEGES;

How to know how much disk space a mysql database used?

select table_schema, sum((data_length+index_length)/1024/1024)AS MB from information_schema.tables groupby1;

How to know the total number of rows in a table?

select table_rows from information_schema.tables where table_schema=’dababasename’ and table_name=’tablename’;

You must be told to get the # records using “select count(*) from tablename”. However, the speed is too slow for large table. information_schema.tables stores useful information about all tables of all databases in the system. But the terminology is a little confusing, for example, it calls the database as   table_schema.

how to show fields one column per line?

By default, the output of select command will display a row per line. If there are many columns for a row, the column fields cannot be display on  a single line. You can append a \G to the end of the command to display the result one field per line.

How to repair corrupted database?

use mysqlchk or myisamchk to check/optimize/recover/fix databases/tables.

How to import csv file to database?

LOAD DATA LOCAL INFILE ‘C:/directoryto/file.txt’ INTO TABLE tablename FIELDS TERMINATED BY ‘|’ ENCLOSED BY ” LINES TERMINATED BY ‘\r\n’

how to execute mysql command in shell?

mysql -u user -pxxxx databasename -e “select * from tablename”

Common errors and fix

Got a packet bigger than ‘max_allowed_packet’ bytes (ERROR 1153 )

 

add a line under [mysqld] section in /etc/my.cnf

max_allowed_packet=500M

 

Unknown character set: ‘utf8mb4′(ERROR 1115)

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

Posted in tips of hosting