docker 安装mysql记录

[root@bogon ~]# docker search mysql  #查看docker仓库中的mysql命令

[root@bogon ~]#

 

[root@bogon ~]# docker pull mysql:5.7 #为选定的需要pull到本地系统中的镜像

5.7: Pulling from library/mysql

45b42c59be33: Pull complete

b4f790bd91da: Pull complete

325ae51788e9: Pull complete

adcb9439d751: Pull complete

174c7fe16c78: Pull complete

698058ef136c: Pull complete

4690143a669e: Pull complete

66676c1ab9b3: Pull complete

25ebf78a38b6: Pull complete

a6510e5d6228: Pull complete

90ca045d52c5: Pull complete

Digest: sha256:9fc60b229633ce1d1f2ee306705152d4b001056fb27c1b5debe23a732df72b70

Status: Downloaded newer image for mysql:5.7

docker.io/library/mysql:5.7

[root@bogon ~]#

[root@bogon ~]# docker images #查看镜像

REPOSITORY   TAG       IMAGE ID       CREATED        SIZE

mysql        5.7       d54bd1054823   18 hours ago   449MB

[root@bogon ~]#

 

Docker	Description
/var/lib/mysql	MySQL data dir
/var/log/mysql	MySQL log dir
/var/sock/mysqld	MySQL socket dir
/etc/mysql/conf.d	MySQL configuration directory (used to overwrite MySQL config)
/etc/mysql/docker-default.d	MySQL configuration directory (used to overwrite MySQL config)

 

#根据上面的镜像创建容器

docker run --name mysql3307 -p 3307:3306 --privileged=true -ti -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=jkpms_prod -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -v /home/mysql/docker-data/3307/conf:/etc/mysql/conf.d -v /home/mysql/docker-data/3307/data/:/var/lib/mysql -v /home/mysql/docker-data/3307/logs/:/var/log/mysql -d mysql:5.7 --lower_case_table_names=1

其中：

-p 3307:3306 　　　　　　　　　　#将容器3306端口映射到宿主机的3307

-e MYSQL_ROOT_PASSWORD=root 　　#设置root用户密码root

-e MYSQL_DATABASE=jkpms_prod 　　#创建数据库jkpms_prod

新建user用户，密码pass

-v 挂载

--restart=always 　　　　　　　　#跟随docker启动 

--privileged=true 　　　　　　　　#容器root用户享有主机root用户权限 

-d 后台启动 

--lower_case_table_names=1 　　#设置表名参数名等忽略大小写

 

[root@begon ~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent

success

[root@begon ~]# netstat -ntpl   

tcp6       0      0 :::3306                 :::*                    LISTEN      31928/mysqld        

[root@begon ~]#

 

 

 

 

[root@bogon 3307]# pwd #当前位置

/home/mysql/docker-data/3307

[root@bogon 3307]#

[root@bogon 3307]# ll

总用量 4

drwxr-xr-x. 2 root    root   20 2月  28 00:40 conf

drwxr-xr-x. 6 polkitd root 4096 2月  28 00:48 data

drwxr-xr-x. 2 root    root    6 2月  28 00:23 logs

[root@bogon 3307]#

 

从现有服务器导表结构和数据到目标服务器：

1、先从数据源导出表结构和数据

# 剔除指定表，导出指定数据库备份到 jkpms_0207.sql

[root@localhost ]# mysqldump -uroot -pRoot123456!  jkpms_prod --default-character-set=utf8 --opt -Q -R --skip-lock-tables --ignore-table=jkpms_prod.t_work_log --ignore-table=jkpms_prod.sys_log_operation > jkpms_0207.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ]#

[root@localhost ]# ll

总用量 1045272

-rw-r--r-- 1 root root 149502374 2月   7 09:51 jkpms_0207.sql

[root@localhost ]# du -sh jkpms_0207.sql # 查看文件大小

143M    jkpms_0207.sql

[root@localhost ]# tar -zcvf jkpms_0207.sql.tar.gz jkpms_0207.sql    # 压缩 jkpms_0207.sql 为 jkpms_0207.sql.tar.gz 

jkpms_0207.sql

[root@localhost ]#

[root@localhost ]# ll

总用量 1056012

-rw-r--r-- 1 root root 149502374 2月   7 09:51 jkpms_0207.sql

-rw-r--r-- 1 root root  10994532 2月   7 09:53 jkpms_0207.sql.tar.gz

[root@localhost ]#

[root@localhost ]# du -sh jkpms_0207.sql.tar.gz 

11M     jkpms_0207.sql.tar.gz

[root@localhost ]#

[root@localhost ]# sz  jkpms_0207.sql.tar.gz # 下载

rz

 zmodem trl+C ȡ

 

  100%   10736 KB  429 KB/s 00:00:25       0 Errors..

[root@localhost ]#

[root@localhost ]# rm -rf jkpms_0207.sql # 删除 jkpms_0207.sql

[root@localhost ]# ll

总用量 910012

-rw-r--r-- 1 root root 10994532 2月   7 09:53 jkpms_0207.sql.tar.gz

[root@localhost ]#

 

 

2、将导出的脚本scp到目标服务器，导入数据库

远程传输：

#将导出的脚本拷贝到目标服务器

[root@localhost ] scp xxx.sql root@192.168.42.119:/home/mysql/docker-data/3307/data

输入密码：1

 

[root@localhost ]# rz #上传脚本

rz waiting to receive.

 zmodem trl+C ȡ

 

  100%   13847 KB 13847 KB/s 00:00:01       0 Errors.

 

[root@localhost ]# ll

总用量 13852

drwxr-xr-x 3 root root       18 3月  26 15:44 docker-data

-rw-r--r-- 1 root root 14179748 3月  26 17:28 jkpms_0326.sql.tar.gz

-rw-r--r-- 1 root root     1098 8月  30 2020 mysql_databak.sh

[root@localhost ]#

[root@localhost ]#

[root@localhost ]# tar -zxvf jkpms_0326.sql.tar.gz  #解压

jkpms_0326.sql

[root@localhost ]# ll

总用量 202180

drwxr-xr-x 3 root root        18 3月  26 15:44 docker-data

-rw-r--r-- 1 root root 192845814 3月  26 17:26 jkpms_0326.sql

-rw-r--r-- 1 root root  14179748 3月  26 17:28 jkpms_0326.sql.tar.gz

-rw-r--r-- 1 root root      1098 8月  30 2020 mysql_databak.sh

[root@localhost ]#

[root@localhost ]# mv jkpms_0326.sql /home/mysql/docker-data/3307/data/ #拷贝到挂载目录下，这样就可以在容器里面直接使用

[root@hong ~]# docker exec -it mysql3307 mysql  -ujkpms_prod  -pjkPmS123! -P3306

或者先进入容器内再登入mysql，如下

[root@localhost ~]# docker exec -it 9d54c03eecee /bin/bash #进入容器

root@9d54c03eecee:/# cd /var/lib/mysql #这个刚刚挂载那个目录

root@9d54c03eecee:/# mysql -uroot -proot -P3306

root@9d54c03eecee:/# mysql -hlocalhost -ujkpms_prod  -pjkPmS123! -P3306 #不行换这个用户

mysql> use jkpms_prod;

mysql> source /var/lib/mysql/jkpms_0326.sql;#导入脚本

mysql> select count(1) from t_sys_user;

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

| count(1) |

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

|      403 |

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

1 row in set (0.01 sec)

 

mysql> exit

 

 

#启动docker

[root@hong ~]# service docker start

Redirecting to /bin/systemctl start docker.service

#启动mysql容器

[root@hong ~]# docker start mysql3307

mysql3307

[root@hong data]# cd /home/mysql/docker-data/3307/data

[root@hong data]# rz #上传许雪里控制中心初始化脚本

[root@hong data]# ll | grep xxl_job.sql

-rw-r--r--  1 root    root       9699 11月 16 2019 tables_xxl_job.sql

#登入容器内mysql

[root@hong ~]# docker exec -it mysql3307 /bin/bash

root@ff0b508daeea:/#

root@ff0b508daeea:/# cd /var/lib/mysql

root@ff0b508daeea:/var/lib/mysql# mysql -uroot -pjkPmS123! -P3306

mysql>

mysql> show databases; #显示所有的数据库

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

| Database           |

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

| information_schema |

| jkpms_prod         |

| mysql              |

| performance_schema |

| sys                |

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

5 rows in set (0.01 sec)

 

mysql> create database xxl_job_admin;#创建数据库：xxl_job_admin

Query OK, 1 row affected (0.01 sec)

 

mysql> show databases;

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

| Database           |

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

| information_schema |

| jkpms_prod         |

| mysql              |

| performance_schema |

| sys                |

| xxl_job_admin      |

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

6 rows in set (0.01 sec)

mysql> use xxl_job_admin;#进入数据库

mysql> show tables;

Empty set (0.00 sec)

mysql> source /var/lib/mysql/tables_xxl_job_admin.sql;#建表脚本

mysql> show tables;

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

| Tables_in_xxl_job_admin          |

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

| xxl_job_qrtz_blob_triggers       |

| xxl_job_qrtz_calendars           |

| xxl_job_qrtz_cron_triggers       |

| xxl_job_qrtz_fired_triggers      |

| xxl_job_qrtz_job_details         |

| xxl_job_qrtz_locks               |

| xxl_job_qrtz_paused_trigger_grps |

| xxl_job_qrtz_scheduler_state     |

| xxl_job_qrtz_simple_triggers     |

| xxl_job_qrtz_simprop_triggers    |

| xxl_job_qrtz_trigger_group       |

| xxl_job_qrtz_trigger_info        |

| xxl_job_qrtz_trigger_log         |

| xxl_job_qrtz_trigger_logglue     |

| xxl_job_qrtz_trigger_registry    |

| xxl_job_qrtz_triggers            |

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

16 rows in set (0.00 sec)

 

mysql> 

 

mysql> source /var/lib/mysql/tables_xxl_job.sql;#直接导入建库建表脚本

mysql> show databases;

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

| Database           |

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

| information_schema |

| jkpms_prod         |

| mysql              |

| performance_schema |

| sys                |

| xxl-job            |----------建库脚本里面有

| xxl_job_admin      |

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

7 rows in set (0.00 sec)

 

mysql> use xxl-job;

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

mysql>

mysql> show tables;

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

| Tables_in_xxl-job                |

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

| xxl_job_qrtz_blob_triggers       |

| xxl_job_qrtz_calendars           |

| xxl_job_qrtz_cron_triggers       |

| xxl_job_qrtz_fired_triggers      |

| xxl_job_qrtz_job_details         |

| xxl_job_qrtz_locks               |

| xxl_job_qrtz_paused_trigger_grps |

| xxl_job_qrtz_scheduler_state     |

| xxl_job_qrtz_simple_triggers     |

| xxl_job_qrtz_simprop_triggers    |

| xxl_job_qrtz_trigger_group       |

| xxl_job_qrtz_trigger_info        |

| xxl_job_qrtz_trigger_log         |

| xxl_job_qrtz_trigger_logglue     |

| xxl_job_qrtz_trigger_registry    |

| xxl_job_qrtz_triggers            |

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

16 rows in set (0.00 sec)

 

mysql>

 

 

 

查看docker仓库中的mysql命令

[root@bogon ~]# docker search mysql