Mysql commands

These commands are for the mysql shell. You have to log in to mysql first.

mysql -uroot -p

Show the create table command of an existing table.

mysql>show create table table1;

Show the create database command of an existing database.

mysql>show create database db1;

Display properties of a table.

mysql>describe table1;

Print global information about the mysql server concerning the character set.

mysql>show global variables like 'character_set%';

Change character sets.

mysql>set character_set_server= utf8;
mysql>set character_set_database= utf8;

Rename an integer column.

mysql>alter table table1 change columnnameold columnnamenew int;

Convert the data of a table to a new charset and set the collation

mysql>alter table table1 convert to charset latin1 collate latin1_german1_ci;

Update column

mysql>update table1 set column1='new string' where id=3;

Update column with a back reference to its current content

mysql>update table1 set column1=concat('string1',column1,'string2');

Get the string representation of the hexadecimal values of column1.

mysql>select hex(column1) from table1;

Create a new user / change permissions for an existing user.

mysql>grant all privileges on db1.* to 'username'@'servername' identified by 'password';

Change the password for a user.

mysql>update mysql.user set password=password('newpassword') where user='root';
mysql>flush privileges;

Show permissions.

mysql>show grants for 'username'@'servername';

Export data of table1 into a CSV file

mysql>select * from table1 into outfile '/root/data.csv';

Import a CSV file into table test (This is done at a very high speed)

mysql>load data local infile '/root/data.csv' into table test fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

Create a new table.

mysql>create table test (id int, col1 varchar(32), col2 varchar(32));

Insert a record into table test.

mysql>insert into test (id,col1,col2) values (2,'string1','string2');

Display md5 hash for the string 'password'.

mysql>select MD5('password');

Set a read lock on all tables of all database. Now the data cannot be changed.

mysql>flush tables with read lock;

Remove the lock so that writing into the database is possible again.

mysql>unlock tables;

Remove all permissions (including grant) for a user, but keep its record in the mysql.user table.

mysql>revoke all privileges, grant option from 'user19'@'localhost';

Remove the user completely (permissions and record in mysql.user).

mysql>drop user 'user19'@'localhost';

Check all databases. This is done from the command line.

mysqlcheck -uroot -p --port 3306 --all-databases