Little note/article for everyone who should/need work with mysql using command line interface to perform usuall task. With this "reminder" you will be able to admin/backup/restore anything like a real chief.

 

Using mysql service in command line

 

Connect to mysql service

mysql -h localhost -u root -p
  • localhost : name of your mysql server
  • u : user name. here : root
  • p : this should by an invite to enter the password

 

Display the list of all databases

mysql> show databases;

 

Select a database to work on

mysql> use databasename;

 

Display a list of all tables in a database

mysql> show tables;

 

Display fieds information

mysql> describe tablename;

 

Display the size of all databases

mysql> SELECT table_schema "Databases", sum( data_length + index_length) / 1024 / 1024 "Size of DB in MB" FROM information_schema.TABLES GROUP BY table_schema;

 

Displat the size of a specific database

mysql> SELECT table_schema "Database", sum( data_length + index_length) / 1024 / 1024 "Size of DB in MB" FROM information_schema.TABLES WHERE table_schema = "$DB_NAME" GROUP BY table_schema;
  •  $DB_NAME : Replace by your database name

 

Display the size of all tables in a database

mysql> SELECT table_name AS "Tables", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "$DB_NAME";
  •  $DB_NAME : Replace by your database name

 

Display the size of a specific table in a database

mysql> SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "$DB_NAME" AND table_name = "$TABLE_NAME";
  • $DB_NAME : Replace by your database name
  • $TABLE_NAME : Replace by your table name

 

Create a new database

mysql> create database databasename;

 

Delete a database

mysql> drop database databasename;

 

Delete a database table

mysql> drop table tablename;

 

Using mysql function from console

 

Backup one database, data and structure

mysqldump -u username -p --databases databasename > databasename.sql

 

Backup all databases, data and structure, to one .sql file

mysqldump -uroot -p --all-databases > alldatabases.sql

 

Restore a database from one database backed up .sq

mysql -u username -p databasename < databasename.sql 

 

Restore one database from backed up .sql file which contains all databases

mysql -u username -p --one-database mybase < alldatabases.sql

 

Popular Tags


Deprecated: Non-static method modJoesWordCloudHelper::getModuleContent() should not be called statically in /var/www/clients/client1/web11/web/modules/mod_joeswordcloud/mod_joeswordcloud.php on line 18

Notice: Only variables should be assigned by reference in /var/www/clients/client1/web11/web/modules/mod_joeswordcloud/helper.php on line 228

Search & Share