MySql

lastmod 2026-02-12created 2014-01-1147 edits

MySQL (official pronunciation: play /maɪ ˌɛskjuːˈɛl/; also often pronounced /maɪ ˈsiːkwəl/) is the world's most widely used open-source relational database management system (RDBMS). It is a multithreaded, multi-user, Structured Query Language (SQL) database management system managed and supported by MySQL AB, and, like Qt, it is dual-licensed. One option is the GPL; if you want to use it under a license other than the GPL, it is subject to a traditional intellectual property license.

This support model resembles that of JBoss, which is similar to how the Free Software Foundation applies copyright to its projects. However, it differs from Apache projects, where the code base is copyrighted by individual owners and developed by the community.

MySQL AB develops and maintains a system of sales support and service contracts based on the MySQL license. It also employs collaborators around the world over the Internet. MySQL AB was founded by David Axmark, Allan Larsson, and Michael "Monty" Widenius.

MySQL AB also sells a database management system called MaxDB, whose code base differs from MySQL AB's. It was acquired by Sun Microsystems for 1 billion dollars, and passed to Oracle along with Sun Microsystems when Oracle acquired Sun.

The default port number used by the MYSQL database is 3306.

Category

• MySql:User: User management.
• MySql:Drop: About the DROP statement.
• MySql:Select: About the SELECT statement.
• MySql:Create: About the CREATE statement.
• MySql:Insert: About the INSERT statement.
• MySql:Grant: About the GRANT statement.

Projects

• mysqlclient (python)
• WeSQL - An innovative MySQL distribution that uses S3 as storage

ETC

• Sqldef - Idempotent schema management tool for MySQL, PostgreSQL, and SQLite

mysql program command-line

• mysql -u root -p mysql: Set up the initial MySQL Root user and password. (When it asks for a password right after, just press Enter, since there is no password yet.)
• service mysqld start: Start the MySQL service.
• service mysqld stop: Stop the MySQL service.
• service mysqld restart: Restart the MySQL service.
• mysql < text_file: Applies the queries.

List of MySQL Command

How to use the mysql program is described below. (This lists the commands entered at the mysql> prompt, not the program's command-line arguments.)

create database DB이름;

Creates a DB.

flush privileges;

Reloads the database.

drop database DB이름;

Deletes a DB.

DESCRIBE TABLE이름;

Prints table information.

source 파일명;

Runs the SQL statements in a text file from MySQL (when writing the file name for the source command, you must use "/" instead of "\".)

show databases;

Prints the list of databases

show tables;

Prints the list of tables in the selected database.

mysql dump

mysqldump -u[userId] -p[password] --all-databases > dump.sql

Creates a logical backup of everything on the server

mysqldump -u[userId] -p[password] --databases [DB명] > dump.sql

Creates a logical backup of specific databases only

mysqldump -u[userId] -p[password] [DB명] [테이블명]

Creates a logical backup of a specific table in a specific database.

Options

• -A, --all-databases : Dumps all DBs
• -B, --databases : Dumps specific DBs
• --opt : Disables buffering and writes large amounts of data into a small number of SQL statements in the dump, so it runs more efficiently.
• --allow-keywords, --quote-names : Lets you dump and restore tables that use reserved words.
• --lock-alltables : Uses "FLUSH TABLES WITH READ LOCK" to make a globally consistent backup.
• --tab : Dumps to files with "SELECT INTO OUTFILE", so dumping and restoring are very fast.
• -d, --no-data : Dumps only the schema, excluding the data
• -t, --no-create-info : Excluding the schema

Connecting to the MySQL Server

• Connecting to the MySQL Server

My.cnf

If bind-address exists in /etc/my.cnf, comment it out with #.

Access

Connect as follows.

$ mysql --host=localhost --user=myname --password=mypass mydb

Or

$ mysql -h localhost -u myname -pmypass mydb

PHP MYSQL

To use MYSQL from PHP, you need to install php-mysql.

## CentOS install:
$ sudo yum install php php-mysql

Tips

17 things not to do when using MySQL

• 17 things not to do when using MySQL – Lael's World
• Thinking small
• Never trying EXPLAIN
• Choosing the wrong data types
• Using pconnect in PHP
• Having an excessive DB abstraction layer
• Not understanding storage engines
• Not understanding index layouts
• Not understanding the query cache
• Using Stored Procedures
• Applying functions to indexed columns
• Missing indexes or creating useless ones
• Not using Join
• Not considering Deep Scan
• Running SELECT COUNT(*) without a WHERE clause on InnoDB tables
• Not profiling or benchmarking
• Not using AUTO_INCREMENT
• Not using ON DUPLICATE KEY UPDATE

Changing the MySQL default encoding

• MySQL : Setting the MySQL default character set
• When Korean input does not work in MariaDB 10.1
• MariaDB utf-8 settings
• Fixing garbled Korean text in MySQL (UTF8)

Change to UTF-8:

[mysqld]
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect=SET NAMES utf8
character-set-server = utf8

Docker settings

• Configuring UTF8 Collation for MySQL or Mariadb on Docker

version: '3.1'

services:

  db:
    image: mariadb
    restart: always
    command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci --init-connect='SET NAMES UTF8;' --innodb-flush-log-at-trx-commit=0
    ports:
      - 3306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=example
      - MYSQL_DATABASE=test
      - MYSQL_USER=testuser
      - MYSQL_PASSWORD=testpassword

Troubleshooting

Docker command error

Passing an argument that contains spaces, such as --init-connect='SET NAMES UTF8;', to the command of Docker-compose causes a problem.

For reference, when passing it on the command line, using the grave (`) character solves it: e.g. --init-connect=`SET NAMES UTF8;`

If that does not work either, you can switch to adding a *.cnf file as a volume in the /etc/mysql/conf.d/ folder. <syntaxhighlight lang="yaml> version: '3.3'

services:

db:
image: mariadb
deploy:
replicas: 1
restart_policy:
condition: any
networks:
- net
secrets:
- portus-db-pw
environment:
MYSQL_DATABASE: portus_production
MYSQL_ROOT_PASSWORD_FILE: /run/secrets/portus-db-pw
#command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci --init-connect='SET NAMES UTF8;' --innodb-flush-log-at-trx-commit=0
command: mysqld
volumes:
- /opt/opm/portus/db/portus.cnf:/etc/mysql/conf.d/portus.cnf:ro

</syntaxhighlight>

Favorite site

• MySQL website
• Wikipedia (en) page on MySQL

Documentation

• Getting Information About Databases and Tables

Tutorials

• MySQL 4.1 Reference Manual

Guide

• MySQL access denied error: ERROR 1045 (28000): Access denied for user
• MySQL initial password setup failure ¹
• Creating users and databases in MySQL ²
• Adding and deleting MySQL users
• MySQL user management (add, delete, grant privileges)
• Zetawiki: Allowing remote access to MySQL
• Zetawiki: MySQL access denied error
• [Recommended] MySQL data types
• Troubleshooting MySQL lock situations (INNODB_LOCK_WAITS Table)

Tip

• 17 things not to do when using MySQL
• [Recommended] 50 Tips for Boosting MySQL Performance ³

Performance

• Improving service performance: based on MySQL Optimizer execution plan analysis

References