Working with Databases

Command-line commands for common tasks. (http://www.pantz.org/software/mysql/mysqlcommands.html)

List Databases

SHOW DATABASES;

Switch to a Database

USE DB_NAME;

List Tables in a Database

SHOW TABLES:

Create a database

/usr/local/mysql/bin/mysqladmin -u <username> -p create <databasename>

Create a user for a database
Login to the MySQL server:

/usr/local/mysql/bin/mysql -u <username> -p

Create the user and grant them privileges:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX,  CREATE
TEMPORARY TABLES, LOCK TABLES
ON databasename.*
TO 'username'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Export a database

/usr/local/mysql/bin/mysqldump -u username -p database_name > FILE.sql

Compress at the same time:

/usr/local/mysql/bin/mysqldump -u username -p database_name | gzip -9 > File.gz

Import a database

/usr/local/mysql/bin/mysql -u username -p database_name < FILE.sql

Large databases
Import/Export Large MySQL Databases courtesy of the Drupal experts at Lullabot

To speed up the import of huge (> 1 GB) SQL dumps, you can add the following two lines to the [mysqld] section of my.cnf and restart MySQL:

[mysqld]
# Performance settings used for import.
delay_key_write=ALL
bulk_insert_buffer_size=256M

Recent Updates

  • 3 months 3 weeks ago
  • 3 months 3 weeks ago
  • 3 months 3 weeks ago
    php 8.x
  • 3 months 3 weeks ago
    10.6.7
  • 3 months 4 weeks ago
    Drop Centos 5/6 stuff