MySQL Basic¶
A popular open source DBMS.
- bought by Oracle
- has an open-source fork, MariaDB
Files¶
Config file
- e.g. can change the location of database files
/etc/my.cnf
Database files
- each directory represents a database with the same name
- has some databases by default, like
mysql
andtest
/var/lib/mysql/
Log
/var/log/mysqld.log
Basic Commands¶
Use mysqladmin
- if password is omitted, will prompt user to input later
mysqladmin -u root -p <some-password> <some-commend>
mysqladmin --user=root --password=<some-old-password> password "some-new-password" # change password
mysqldump -R some_db > dump
Connect to local mysql server
- -u user
- -p login using password
- -h specify host
mysql -u <some-user-name> -p
> input your password
mysql -h 192.168.1.1 -u root -p
Cannot login unless sudo
?
ERROR 1698 (28000): Access denied for user ‘root’@’localhost’
Log out mysql shell
mysql>
mysql> quit
Sample queries
SELECT VERSION();
SELECT CURRENT_DATE;
SELECT VERSION(), CURRENT_DATE\g
SELECT VERSION(), CURRENT_DATE\G
SELECT 4+4;
Databases commands
SHOW DATABASES;
USE some_database;
CREATE DATABASE some_database;
DROP DATABASE some_database;
Simple Backup¶
mysqldump -u root -p some-database-name > some-backup-file-name.sql
mysql -u root -p some-database-name < some-backup-file-name.sql
Character Set and Collation¶
A database uses specific encoding for the data.
Usually use different encoding for different languages
Why different?
- Storage size
- Communication between database and client
It should be
- The data are stored using the minimum space
- Same char set should be used otherwise the data will be garbled text
MySQL¶
Default:
char set: latin1
collation: latin1_swedish_ci
Char set and collation should be changed in the same time.
List supported char set
SHOW CHARACTER SET;
The most commonly used one is UTF-8.
List environment variables
SHOW VARIABLES;
List current config
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
Specify when creating databases
CREATE DATABASE db DEFAULT CHARACTER_SET utf8 DEFAULT COLLATE utf8_general_ci;
Change existing ones
- Note the existing records might not be encoded correctly
- They must be dropped and recreated
ALTER DATABASE db CHARACTER SET utf8 COLLATE utf8_general_ci;
Default Character Set and Collation Config¶
The file is located at /etc/my.cnf
.
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
default-character-set=utf8
collation-server=uft8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server=utf8
Reload mysql after changing
MySQL :: MySQL 8.0 Reference Manual :: 4.2.7 Using Option Files