lsoci mysql_flask项目从sqlite3升级的mysql数据库

flask项目因为需求变更，需要迁移到mysql数据库。

项目计划采用docker来管理，采用mysql官网的docker来管理。

删除数据库目录。

sudo rm -rf data/

删除原日志目录。

$ rm -rf log/

修改docker-compose.yml 文件。

从原来一个项目中复制过来的docker-compose.yml文件，内容如下：

estimate:

# restart: always

build: ./app

ports:

- "8000:8000"

links:

- mysql:mysql

volumes:

- ./app:/usr/src/app

env_file: .env

mysql:

# restart: always

image: mysql:latest

env_file: .env

ports:

- "3306:3306"

volumes:

- ./data:/var/lib/mysql

- ./data/my.cnf:/etc/mysql/my.cnf

environment:

- MYSQL_ROOT_PASSWORD=estimate

配置文件改好了，准备构建镜像：

docker-compose build

看起来一切顺利。

ERROR: for mysql oci runtime error: rootfs_linux.go:53: mounting "/var/lib/docker/aufs/mnt/258ac98827bf738a0196693883820ebb3e4801320a2a3542a39076edf9ef1649/etc/mysql/my.cnf" to rootfs "/var/lib/docker/aufs/mnt/258ac98827bf738a0196693883820ebb3e4801320a2a3542a39076edf9ef1649" caused "not a directory"

Traceback (most recent call last):

File "/usr/local/bin/docker-compose", line 9, in

load_entry_point('docker-compose==1.7.1', 'console_scripts', 'docker-compose')()

File "/usr/local/lib/python2.7/dist-packages/compose/cli/main.py", line 63, in main

log.error(e.msg)

AttributeError: 'ProjectError' object has no attribute 'msg'

看起来情况不太妙，原因来配置不能正常拉起来，貌似my.cnf不是一个目录的原因，改为映射/etc/mysql试了一下，mysql是初始化成功了，但是容器与映射目录中均没有my.cnf文件，暂不知道原因，先去掉对my.cnf的映射，再次尝试：

$ docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

6112bf03d615 estimate_estimate "/usr/local/bin/super" 29 seconds ago Up 28 seconds 5000/tcp, 0.0.0.0:8000->8000/tcp estimate_estimate_1

5bed08089c16 mysql:latest "docker-entrypoint.sh" 30 seconds ago Up 29 seconds 0.0.0.0:3306->3306/tcp estimate_mysql_1

现在看起来正常多了。

4. 修改mysql的默认字符集为utf8,修改配置文件，mysql要注意不后字符集不对，后面很麻烦。

$ docker exec -it 5bed08089c16 /bin/bash

root@5bed08089c16:/# cd /etc/

root@5bed08089c16:/etc# ll

bash: ll: command not found

root@5bed08089c16:/etc# cd mysql

root@5bed08089c16:/etc/mysql# ll

bash: ll: command not found

root@5bed08089c16:/etc/mysql# ls

conf.dmy.cnf

root@5bed08089c16:/etc/mysql# vim my.cnf

bash: vim: command not found

root@5bed08089c16:/etc/mysql# vi my.cnf

bash: vi: command not found

root@5bed08089c16:/etc/mysql#

郁闷了，容器中没有vim，连vi也没有，还好有apt安装吧。。。。

root@5bed08089c16:/etc/mysql# apt-get install vim

Reading package lists... Done

Building dependency tree

Reading state information... Done

E: Unable to locate package vim

root@5bed08089c16:/etc/mysql#

什么情况，貌似，源也不正常。。。

apt-get update

apt-get install vim

不是国内源，比较慢，等的时间有点长。。。终于好了。

连接mysql查看字符集设置情况。

root@5bed08089c16:/etc/mysql# mysql -u root -p

Enter password:

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

Your MySQL connection id is 2

Server version: 5.7.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql> show variables like '%character%';

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

| Variable_name | Value |

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

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

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

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

8 rows in set (0.00 sec)

准备修改字符集配置

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

default-character-set=utf8 //added by janl

[mysqld_safe]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

skip-host-cache

skip-name-resolve

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

character_set_server=utf8 // added by janl

init_connect='SET NAMES utf8' //added by janl

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc-messages-dir = /usr/share/mysql

explicit_defaults_for_timestamp

好了，让容器重启一下，让配置生效。

再次查看mysql 的字符集配置

mysql> show variables like '%character%';

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

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

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

8 rows in set (0.01 sec)

看来字符集都改过来了，一切顺利，创建数据库。

创建数据库

mysql> CREATE DATABASE IF NOT EXISTS estimate DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| estimate |

| mysql |

| performance_schema |

| sys |

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

5 rows in set (0.00 sec)

mysql> use estimate;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql>

数据库建立起来了，然后就可以和数据库一起愉快的玩耍了。。。。。。。。