mysql 修改字段类型 production_实验：实现多实例 、数据库的create和alter、create表...

多实例：软件程序运行了多次，规划：第一个端口在“ 3306 ”，第二个端口在“ 3307 ”，第三个端口在“ 3308 ”，在对外提供服务时，就感觉是三个mysql服务器一样。(此实验与mariadb的版本无关)

1、安装mariadb，[root@centos7 ~]# yum install mariadb

Loaded plugins: fastestmirror, langpacks

Loading mirror speeds from cached hostfile

Installed:

mariadb.x86_64 1:5.5.60-1.el7_5

Complete!

[root@xiqukeke ~]# systemctl start mariadb

[root@xiqukeke ~]#

此时生成了数据库文件：

[root@xiqukeke ~]# ls /var/lib/mysql/

aria_log.00000001 aria_log_control ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock performance_schema

既然要实现多实例，每个实例都要有自己独立的数据库文件，我们建三个文件夹，分别存放实例的数据库，如下：

[root@xiqukeke ~]# mkdir /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}/ -pv

mkdir: created directory ‘/mysql’

mkdir: created directory ‘/mysql/3306’

mkdir: created directory ‘/mysql/3306/data/’

mkdir: created directory ‘/mysql/3306/etc/’

mkdir: created directory ‘/mysql/3306/socket/’

mkdir: created directory ‘/mysql/3306/log/’

mkdir: created directory ‘/mysql/3306/bin/’

mkdir: created directory ‘/mysql/3306/pid/’

mkdir: created directory ‘/mysql/3307’

mkdir: created directory ‘/mysql/3307/data/’

mkdir: created directory ‘/mysql/3307/etc/’

mkdir: created directory ‘/mysql/3307/socket/’

mkdir: created directory ‘/mysql/3307/log/’

mkdir: created directory ‘/mysql/3307/bin/’

mkdir: created directory ‘/mysql/3307/pid/’

mkdir: created directory ‘/mysql/3308’

mkdir: created directory ‘/mysql/3308/data/’

mkdir: created directory ‘/mysql/3308/etc/’

mkdir: created directory ‘/mysql/3308/socket/’

mkdir: created directory ‘/mysql/3308/log/’

mkdir: created directory ‘/mysql/3308/bin/’

mkdir: created directory ‘/mysql/3308/pid/’

[root@xiqukeke ~]# tree /mysql/

/mysql/

├── 3306

│   ├── bin

│   ├── data

│   ├── etc

│   ├── log

│   ├── pid

│   └── socket

├── 3307

│   ├── bin

│   ├── data

│   ├── etc

│   ├── log

│   ├── pid

│   └── socket

└── 3308

├── bin

├── data

├── etc

├── log

├── pid

└── socket

21 directories, 0 files

3、我们先把/mysql属组权限更改一下，并把数据/data/放入：

[root@xiqukeke ~]# getent passwd mysql

mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin

[root@xiqukeke ~]# ll /mysql/

total 0

drwxr-xr-x 8 root root 76 Feb 10 21:41 3306

drwxr-xr-x 8 root root 76 Feb 10 21:41 3307

drwxr-xr-x 8 root root 76 Feb 10 21:41 3308

[root@xiqukeke ~]# chown -R mysql.mysql /mysql/

[root@xiqukeke ~]#

[root@xiqukeke ~]# which mysql_install_db

/usr/bin/mysql_install_db

[root@xiqukeke ~]# mysql_install_db --datadir=/mysql/3306/data/ --user=mysql

Installing MariaDB/MySQL system tables in '/mysql/3306/data/' ...

190210 21:47:48 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 74207 ...

OK

Filling help tables...

190210 21:47:49 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 74215 ...

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !

To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'

'/usr/bin/mysqladmin' -u root -h xiqukeke password 'new-password'

Alternatively you can run:

'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test

databases and anonymous user created by default. This is

strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the

MySQL manual for more instructions.

You can start the MariaDB daemon with:

cd '/usr' ; /usr/bin/mysqld_safe --datadir='/mysql/3306/data/'

You can test the MariaDB daemon with mysql-test-run.pl

cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.

You can find additional information about the MySQL part at:

http://dev.mysql.com

Consider joining MariaDB's strong and vibrant community:

https://mariadb.org/get-involved/

此时，/data数据库已然生成，如下：

[root@xiqukeke ~]# tree /mysql/3306

/mysql/3306

├── bin

├── data

│?? ├── aria_log.00000001

│?? ├── aria_log_control

│?? ├── mysql

│?? │?? ├── columns_priv.frm

│?? │?? ├── columns_priv.MYD

│?? │?? ├── columns_priv.MYI

│?? │?? ├── db.frm

│?? │?? ├── db.MYD

│?? │?? ├── db.MYI

│?? │?? ├── event.frm

│?? │?? ├── event.MYD

│?? │?? ├── event.MYI

│?? │?? ├── func.frm

│?? │?? ├── func.MYD

│?? │?? ├── func.MYI

│?? │?? ├── general_log.CSM

│?? │?? ├── general_log.CSV

│?? │?? ├── general_log.frm

│?? │?? ├── help_category.frm

│?? │?? ├── help_category.MYD

│?? │?? ├── help_category.MYI

│?? │?? ├── help_keyword.frm

│?? │?? ├── help_keyword.MYD

│?? │?? ├── help_keyword.MYI

│?? │?? ├── help_relation.frm

│?? │?? ├── help_relation.MYD

│?? │?? ├── help_relation.MYI

│?? │?? ├── help_topic.frm

│?? │?? ├── help_topic.MYD

│?? │?? ├── help_topic.MYI

│?? │?? ├── host.frm

│?? │?? ├── host.MYD

│?? │?? ├── host.MYI

│?? │?? ├── ndb_binlog_index.frm

│?? │?? ├── ndb_binlog_index.MYD

│?? │?? ├── ndb_binlog_index.MYI

│?? │?? ├── plugin.frm

│?? │?? ├── plugin.MYD

│?? │?? ├── plugin.MYI

│?? │?? ├── proc.frm

│?? │?? ├── proc.MYD

│?? │?? ├── proc.MYI

│?? │?? ├── procs_priv.frm

│?? │?? ├── procs_priv.MYD

│?? │?? ├── procs_priv.MYI

│?? │?? ├── proxies_priv.frm

│?? │?? ├── proxies_priv.MYD

│?? │?? ├── proxies_priv.MYI

│?? │?? ├── servers.frm

│?? │?? ├── servers.MYD

│?? │?? ├── servers.MYI

│?? │?? ├── slow_log.CSM

│?? │?? ├── slow_log.CSV

│?? │?? ├── slow_log.frm

│?? │?? ├── tables_priv.frm

│?? │?? ├── tables_priv.MYD

│?? │?? ├── tables_priv.MYI

│?? │?? ├── time_zone.frm

│?? │?? ├── time_zone_leap_second.frm

│?? │?? ├── time_zone_leap_second.MYD

│?? │?? ├── time_zone_leap_second.MYI

│?? │?? ├── time_zone.MYD

│?? │?? ├── time_zone.MYI

│?? │?? ├── time_zone_name.frm

│?? │?? ├── time_zone_name.MYD

│?? │?? ├── time_zone_name.MYI

│?? │?? ├── time_zone_transition.frm

│?? │?? ├── time_zone_transition.MYD

│?? │?? ├── time_zone_transition.MYI

│?? │?? ├── time_zone_transition_type.frm

│?? │?? ├── time_zone_transition_type.MYD

│?? │?? ├── time_zone_transition_type.MYI

│?? │?? ├── user.frm

│?? │?? ├── user.MYD

│?? │?? └── user.MYI

│?? ├── performance_schema

│?? │?? ├── cond_instances.frm

│?? │?? ├── db.opt

│?? │?? ├── events_waits_current.frm

│?? │?? ├── events_waits_history.frm

│?? │?? ├── events_waits_history_long.frm

│?? │?? ├── events_waits_summary_by_instance.frm

│?? │?? ├── events_waits_summary_by_thread_by_event_name.frm

│?? │?? ├── events_waits_summary_global_by_event_name.frm

│?? │?? ├── file_instances.frm

│?? │?? ├── file_summary_by_event_name.frm

│?? │?? ├── file_summary_by_instance.frm

│?? │?? ├── mutex_instances.frm

│?? │?? ├── performance_timers.frm

│?? │?? ├── rwlock_instances.frm

│?? │?? ├── setup_consumers.frm

│?? │?? ├── setup_instruments.frm

│?? │?? ├── setup_timers.frm

│?? │?? └── threads.frm

│?? └── test

├── etc

├── log

├── pid

└── socket

9 directories, 92 files

同理，3307,3308的数据库文件生成步骤同上，就不一一例举了。

我们查看3307文件的详情，可看到所有者，所属组都是mysql了，如下：

[root@xiqukeke ~]# ll /mysql/3307/

total 0

drwxr-xr-x 2 mysql mysql 6 Feb 10 21:41 bin

drwxr-xr-x 5 mysql mysql 106 Feb 10 22:15 data

drwxr-xr-x 2 mysql mysql 6 Feb 10 21:41 etc

drwxr-xr-x 2 mysql mysql 6 Feb 10 21:41 log

drwxr-xr-x 2 mysql mysql 6 Feb 10 21:41 pid

drwxr-xr-x 2 mysql mysql 6 Feb 10 21:41 socket

[root@xiqukeke ~]# ll /mysql/3307/data

total 28

-rw-rw---- 1 mysql mysql 16384 Feb 10 22:15 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Feb 10 22:15 aria_log_control

drwx------ 2 mysql root 4096 Feb 10 22:15 mysql

drwx------ 2 mysql mysql 4096 Feb 10 22:15 performance_schema

drwx------ 2 mysql root 6 Feb 10 22:15 test

修改配置文件，内容如下：

[root@xiqukeke ~]# cp /etc/my.cnf /mysql/3306/etc/

[root@xiqukeke ~]# vim /mysql/3306/etc/my.cnf

[mysqld]

port=3306

init_connect='SET collation_connection = utf8_unicode_ci'

init_connect='SET NAMES utf8'

character-set-server=utf8

collation-server=utf8_unicode_ci

skip-character-set-client-handshake

datadir=/mysql/3306/data

socket=/mysql/3306/socket/mysql.sock

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

Settings user and group are ignored when systemd is used.

If you need to run mysqld under a different user or group,

customize your systemd unit file for mariadb according to the

[mysqld_safe]

log-error=/mysql/3306/log/mariadb.log

pid-file=/mysql/3306/pid/mariadb.pid

include all files from the config directory

同理，3307,3308修改内容同上。

现在把mysql服务down机，

[root@xiqukeke /mysql/3306/bin]# mysqladmin -uroot -p12345gxy shutdown

[root@xiqukeke /mysql/3306/bin]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 :111 已经无3306端口了:LISTEN 0 128:6000 :

LISTEN 0 5 192.168.122.1:53 :

LISTEN 0 128 :22:LISTEN 0 128 127.0.0.1:631:LISTEN 0 100 127.0.0.1:25:LISTEN 0 128 127.0.0.1:6010:LISTEN 0 128 127.0.0.1:6011:LISTEN 0 128 127.0.0.1:6012:LISTEN 0 128 127.0.0.1:6013:LISTEN 0 128 127.0.0.1:6014:LISTEN 0 128 :::111 :::

LISTEN 0 128 :::80 :::LISTEN 0 128 :::6000 :::

LISTEN 0 128 :::22 :::LISTEN 0 128 ::1:631 :::

LISTEN 0 100 ::1:25 :::LISTEN 0 128 ::1:6010 :::

LISTEN 0 128 ::1:6011 :::LISTEN 0 128 ::1:6012 :::

LISTEN 0 128 ::1:6013 :::LISTEN 0 128 ::1:6014 :::

正好把它停了，我们要开启多实例就用不到它了：

root@centos7 ~]#cd /mysql/3306/bin

[root@centos7 bin]#ls

[root@centos7 bin]#rz

此处rz到的是从本地桌面传来的mysqld文件

[root@xiqukeke /mysql/3306/bin]# vim mysqld

#!/bin/bash

port=3306

mysql_user="root"

mysql_pwd=""

cmd_path="/usr/bin"

mysql_basedir="/mysql"

mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

该文件主要修改如上内容！

[root@xiqukeke /mysql/3306/bin]# vim ../../3307/bin/mysqld

[root@xiqukeke /mysql/3306/bin]# vim ../../3308/bin/mysqld

[root@xiqukeke /mysql/3306/bin]# pwd

/mysql/3306/bin

[root@xiqukeke /mysql/3306/bin]# ll

total 4

-rw-r--r-- 1 root root 997 Feb 10 22:49 mysqld

[root@xiqukeke /mysql/3306/bin]# chmod +x mysqld

[root@xiqukeke /mysql/3306/bin]# chmod +x ../../3307/bin/mysqld

[root@xiqukeke /mysql/3306/bin]# chmod +x ../../3308/bin/mysqld

[root@xiqukeke /mysql/3306/bin]# (同上)

启动脚本：

[root@xiqukeke /mysql/3306/bin]# ../../3307/bin/mysqld start

Starting MySQL...

[root@xiqukeke /mysql/3306/bin]# ../../3308/bin/mysqld start

Starting MySQL...

[root@xiqukeke /mysql/3306/bin]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 50 :3306 三个端口均启动:LISTEN 0 50:3307 :

LISTEN 0 50 :3308:LISTEN 0 128:111 :

1、[root@xiqukeke /mysql/3306/bin]# mysql -S /mysql/3306/socket/mysql.sock

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> status

mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 2

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server: MariaDB

Server version: 5.5.60-MariaDB MariaDB Server

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /mysql/3306/socket/mysql.sock

Uptime: 8 min 43 sec

Threads: 1 Questions: 7 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.013

MariaDB [(none)]>

2、[root@xiqukeke ~]# mysql -S /mysql/3307/socket/mysql.sock

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> status

mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 1

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server: MariaDB

Server version: 5.5.60-MariaDB MariaDB Server

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /mysql/3307/socket/mysql.sock

Uptime: 8 min 34 sec

Threads: 1 Questions: 4 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.007

MariaDB [(none)]> 可以清楚看到3306,3307的状态。

为了安全进入，我们要修改密码，实现只有输入口令才能登陆，操作如下：

[root@xiqukeke /mysql/3306/bin]# mysqladmin -S /mysql/3306/socket/mysql.sock password '12345gxy'

[root@xiqukeke /mysql/3306/bin]# 此时口令修改成功

[root@xiqukeke /mysql/3306/bin]# mysql -S /mysql/3306/socket/mysql.sock -u root -p12345gxy

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 6

Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [mysql]> select user,host,password from user;

+------+-----------+-------------------------------------------+

| user | host | password |

+------+-----------+-------------------------------------------+

| root | localhost | CBCBFB504BD0296079FB8D7A5C4A70627C3BC24A |

| root | xiqukeke | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | xiqukeke | |

+------+-----------+-------------------------------------------+

6 rows in set (0.00 sec)

MariaDB [mysql]> 此时，3306的口令已经设置完成，同理，3307,3308，照例修改。

我们现在想要暂时关闭数据库，需要输入口令才能关闭，

[root@xiqukeke /mysql/3306/bin]# pwd

/mysql/3306/bin

[root@xiqukeke /mysql/3306/bin]# ./mysqld stop

Stoping MySQL...

Enter password:

只要修改脚本，就能实现自动关闭，修改内容如下：

[root@xiqukeke /mysql/3306/bin]# vim mysqld

#!/bin/bash

port=3306

mysql_user="root"

mysql_pwd="12345gxy" cmd_path="/usr/bin"

mysql_basedir="/mysql"

mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

[root@xiqukeke /mysql/3306/bin]# vim ../../3307/bin/mysqld

[root@xiqukeke /mysql/3306/bin]# vim ../../3308/bin/mysqld 同理

看到端口已关闭，如下：

[root@xiqukeke /mysql/3306/bin]# ../../3307/bin/mysqld stop

Stoping MySQL...

[root@xiqukeke /mysql/3306/bin]# ../../3308/bin/mysqld stop

Stoping MySQL...

[root@xiqukeke /mysql/3306/bin]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128:111 :

LISTEN 0 128 :6000:LISTEN 0 5 192.168.122.1:53:LISTEN 0 128:22 :

LISTEN 0 128 127.0.0.1:631 :

[root@xiqukeke /mysql/3306/bin]# ./mysqld start

Starting MySQL...

[root@xiqukeke /mysql/3306/bin]# ../../3308/bin/mysqld start

Starting MySQL...

[root@xiqukeke /mysql/3306/bin]# ../../3307/bin/mysqld start

Starting MySQL... (此处的开启服务也是一样的。)

到此，多实例服务已经完成。

步骤总结：

1 yum install mariadb

2 创建多实例对应的目录结构

mkdir /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid} -pv

chown -R mysql.mysql /mysql

3 创建多实例的数据库文件

mysql_install_db --datadir=/mysql/3306/data/ --user=mysql

mysql_install_db --datadir=/mysql/3307/data/ --user=mysql

mysql_install_db --datadir=/mysql/3308/data/ --user=mysql

4 创建对应配置文件

cp /etc/my.cnf /mysql/3306/etc

vim /mysql/3306/etc/my.cnf

[mysqld]

port=3306 加一行

datadir=/mysql/3306/data

socket=/mysql/3306/socket/mysql.sock

[mysqld_safe]

log-error=/mysql/3306/log/mariadb.log

pid-file=/mysql/3306/pid/mariadb.pid

cp /mysql/3306/etc/my.cnf /mysql/3307/etc/my.cnf/mysql/3306/etc/my.cnf 修改

cp /mysql/3306/etc/my.cnf /mysql/3308/etc/my.cnf/mysql/3306/etc/my.cnf 修改

5 准备各实例的启动脚本

vi /mysql/{3306,3307,3308}/bin/mysqld

cat /mysq/3306/bin/mysqld

#!/bin/bash

port=3306

mysql_user="root"

mysql_pwd="centos"

cmd_path="/usr/bin"

mysql_basedir="/mysql"

mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()

{

if [ ! -e "$mysql_sock" ];then

printf "Starting MySQL...\n"

${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &

else

printf "MySQL is running...\n"

exit

fi

}

function_stop_mysql()

{

if [ ! -e "$mysql_sock" ];then

printf "MySQL is stopped...\n"

exit

else

printf "Stoping MySQL...\n"

${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown

fi

}

function_restart_mysql()

{

printf "Restarting MySQL...\n"

function_stop_mysql

sleep 2

function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

*)

printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"

esac

chmod +x /mysql/{3306,3307,3308}/bin/mysqld

6 启动和关闭实例

/mysql/{3306,3307,3308}/bin/mysqld start

/mysql/{3306,3307,3308}/bin/mysqld stop

7 测试连接

mysql -S /mysql/{3306,3307,3308}/socket/mysql.sock

8 安全加固

mysqladmin -S /mysql/{3306,3307,3308}/socket/mysql.sock password 'centos'

vi /mysql/{3306,3307,3308}/bin/mysqld 加上对应centos口令

我们打开3306端口的数据库，看到show databases;下，系统默认自带四个数据库：(注：数据库表现为一个文件夹)

[root@xiqukeke /mysql/3306/bin]# mysql -S /mysql/3306/socket/mysql.sock -p12345gxy

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user,host,password from mysql.user;

+------+-----------+-------------------------------------------+

| user | host | password |

+------+-----------+-------------------------------------------+

| root | localhost | *CBCBFB504BD0296079FB8D7A5C4A70627C3BC24A |

| root | xiqukeke | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | xiqukeke | |

+------+-----------+-------------------------------------------+

6 rows in set (0.00 sec)

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00 sec)

MariaDB [(none)]>

接下来，我们创建自己的数据库，快速浏览一下help，show，：

MariaDB [(none)]> help create

Many help items for your request exist.

To make a more specific request, please type 'help ',

where is one of the following

topics:

CREATE DATABASE

CREATE EVENT

CREATE FUNCTION

CREATE FUNCTION UDF

CREATE INDEX

CREATE PROCEDURE

CREATE SERVER

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

CREATE USER

CREATE VIEW

SHOW

SHOW CREATE DATABASE

SHOW CREATE EVENT

SHOW CREATE FUNCTION

SHOW CREATE PROCEDURE

SHOW CREATE TABLE

SPATIAL

MariaDB [(none)]> help create databases ;

Nothing found

Please try to run 'help contents' for a list of all accessible topics

MariaDB [(none)]> help create database ;

Name: 'CREATE DATABASE'

Description:

Syntax:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification] ...

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this

statement, you need the CREATE privilege for the database. CREATE

SCHEMA is a synonym for CREATE DATABASE.

MariaDB [(none)]> show variables like '%chara%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 | 该种字符集用的拉丁文

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

MariaDB [(none)]>

创建“ db1 ”的数据库，MariaDB [(none)]> create database db1;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| db1 |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

[root@xiqukeke ~]# cd /mysql/3306/data

[root@xiqukeke /mysql/3306/data]# ls

aria_log.00000001 aria_log_control db1 ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test

[root@xiqukeke /mysql/3306/data]# tree db1/

db1/

└── db.opt

0 directories, 1 file

[root@xiqukeke /mysql/3306/data]# cd db1/

[root@xiqukeke /mysql/3306/data/db1]# cat db.opt

default-character-set=latin1

default-collation=latin1_swedish_ci

可以看到db1的文件内容如上，下面我们复制一份数据库标为db2，在字符集中也能显示出来，如下：

[root@xiqukeke /mysql/3306/data]# cp db1 db2 -a

[root@xiqukeke /mysql/3306/data]# cd db2

[root@xiqukeke /mysql/3306/data/db2]# ls

db.opt

[root@xiqukeke /mysql/3306/data/db2]# cat db.opt

default-character-set=latin1

default-collation=latin1_swedish_ci

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| db1 |

| db2 |

| mysql |

| performance_schema |

| test |

+--------------------+

6 rows in set (0.00 sMariaDB [(none)]> drop database db2;

Query OK, 0 rows affected (0.00 sec)

删除数据库用“ drop ”：MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| db1 |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

查看db1的字符集是啥类型：MariaDB [(none)]> show create database db1;

+----------+----------------------------------------------------------------+

| Database | Create Database |

+----------+----------------------------------------------------------------+

| db1 | CREATE DATABASE db1 /!40100 DEFAULT CHARACTER SET latin1/ |

+----------+----------------------------------------------------------------+

1 row in set (0.00 sec)

修改数据库类型，使用“ alter ”：MariaDB [(none)]> alter database db1 CHARACTER SET utf8;

Query OK, 1 row affected (0.01 sec)

在文件中查看,已修改：[root@xiqukeke /mysql/3306/data]# cd db1

[root@xiqukeke /mysql/3306/data/db1]# ls

db.opt

[root@xiqukeke /mysql/3306/data/db1]# cat db.opt

default-character-set=utf8

default-collation=utf8_general_ci

假设我们想要创建数据库，但不知道该数据库是否存在，可以用“ IF NOT EXISTS ”进行判断，如果存在就不创建了，如下：

MariaDB [(none)]> create database IF NOT EXISTS db1;

Query OK, 1 row affected, 1 warning (0.00 sec)

因为我们创建数据库时是远程连接的数据库，用命令操作的，上述的方法比较规范，该有的权限等也都已经设置好，所以较为普遍使用。

创建表：

1、制作表：create table student_m35 ((各数据库的类型选择)

id tinyint UNSIGNED AUTO_INCREMENT primary key，value，flag，expire

name varchar (20) not null ,

gender enum('m','f') default 'm',

phone char(11)

);

MariaDB [(none)]> create database test1;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use test1

Database changed

MariaDB [test1]> create table student_m35 ( id tinyint unsigned AUTO_INCREMENT primary key, name varchar (20) not null, gender enum('m','f') default 'm', phone char(11) );

Query OK, 0 rows affected (0.00 sec)

MariaDB [test1]> show tables;

+-----------------+

| Tables_in_test1 |

+-----------------+

| student_m35 |

+-----------------+

1 row in set (0.00 sec)

2、查看m35的字符集类型，是“ latin1 ”：

MariaDB [test1]> show create table student_m35 ;

+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| student_m35 | CREATE TABLE student_m35 (

id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

name varchar(20) NOT NULL,

gender enum('m','f') DEFAULT 'm',

phone char(11) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

切换到“ db1 ”数据库，再次创建m35，查看字符集类型。就和db1一样是“ utf8 ”了，如下：

MariaDB [test1]> use db1;

Database changed

MariaDB [db1]> create table student_m35 ( id tinyint unsigned AUTO_INCREMENT primary key, name varchar (20) not null, gender enum('m','f') default 'm', phone char(11) );

Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> show create table student_m35 ;

+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| student_m35 | CREATE TABLE student_m35 (

id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

name varchar(20) NOT NULL,

gender enum('m','f') DEFAULT 'm',

phone char(11) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

使用“ desc ”工具查看表结构：

MariaDB [db1]> desc student_m35;

+--------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+----------------+

| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |

| name | varchar(20) | NO | | NULL | |

| gender | enum('m','f') | YES | | m | |

| phone | char(11) | YES | | NULL | |

+--------+---------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

我们想要添加一项“ age ”，如下:

MariaDB [db1]> alter table student_m35 add age tinyint unsigned not null default 20 after name;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [db1]> desc student_m35;

+--------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+----------------+

| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |

| name | varchar(20) | NO | | NULL | |

| age | tinyint(3) unsigned | NO | | 20 | |

| gender | enum('m','f') | YES | | m | |

| phone | char(11) | YES | | NULL | |

+--------+---------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

我们进行更改表名，字段赋值，设置中文字幕：

MariaDB [db1]> alter table student_m35 rename student;

Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> desc student;

+--------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+----------------+

| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |

| name | varchar(20) | NO | | NULL | |

| age | tinyint(3) unsigned | NO | | 20 | |

| gender | enum('m','f') | YES | | m | |

| phone | char(11) | YES | | NULL | |

+--------+---------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

MariaDB [db1]> insert into student (name,phone) value ('a','119');

Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> select * from student;

+----+------+-----+--------+-------+

| id | name | age | gender | phone |

+----+------+-----+--------+-------+

| 1 | a | 20 | m | 119 |

+----+------+-----+--------+-------+

1 row in set (0.00 sec)

MariaDB [db1]> select id ,name from student;

+----+------+

| id | name |

+----+------+

| 1 | a |

+----+------+

1 row in set (0.00 sec)

MariaDB [db1]> select id 学员序号,name 学员姓名 from student;

+--------------+--------------+

| 学员序号 | 学员姓名 |

+--------------+--------------+

| 1 | a |

+--------------+--------------+

1 row in set (0.00 sec)