User Tools

Site Tools


mysql

Table of Contents

MySQL

Common tasks

Create database (UTF8):

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Export mysql data:

$ mysqldump -u user -p --routines --default-character-set=utf8 db_name >dump.sql
$ mysqldump -u user -p --opt --routines --add-drop-database --default-character-set=utf8 --databases db_name >dump.sql

Import mysql data:

$ mysql -u user -p --default-character-set=utf8 db_name <dump.sql

Set client ↔ server communication to UTF-8:

SET NAMES 'utf8';

Increase allowed packet size (e.g. for storing large blobs):

SET GLOBAL max_allowed_packet=8M;

Show used engine for all tables of a schema:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA= 'db_name';

Drop key constraints and column at the same time (InnoDB):

ALTER TABLE `topic` 
   DROP FOREIGN KEY `user_id_foreign`, 
   DROP COLUMN `user_id`;

Alter default charset:

ALTER DATABASE tbl_name DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci

Convert table charset:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

“Defragment” database:

$ mysqlcheck -o <database>

Server settings

/etc/mysql/conf.d/custom.cnf
[mysqld]
 
# bind to all interfaces (dangerous!)
#bind-address = 0.0.0.0
 
# set default character set and collation to UTF-8
character_set_server = utf8
collation_server = utf8_general_ci
 
# set default storage engine to InnoDB instead of MyISAM
default-storage-engine = innodb
 
# use per-table tablespaces
#innodb_file_per_table

Using Per-Table Tablespaces

mysql.txt · Last modified: 2014-06-08 11:47 CEST by dominik