Working with Databases

Primary tabs

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

  • nginx
    7 months 2 weeks ago
    1.27.2 update
  • lighttpd
    7 months 4 weeks ago
    Drupal 10/11 config
  • Drupal 11
    8 months 1 day ago
  • PHP
    8 months 1 day ago
    PHP 8.3.11 and AlmaLinux
  • Pound SSL Proxy
    8 months 1 day ago
    New version of Pound