From a Python container launched with Docker I was addicted to various things when using the MySQL container, so I will summarize it.
3.5.2-alpine)mysql command and can't connect on the command lineWhen developing using Docker I think there are many cases where Alpine Linux is used to reduce the weight of the container. While lightweight and quick to boot, many commands have to be installed on their own.
When connecting to a MySQL container from an Alpine Linux container on the command line
Of course, the mysql command is not included, so
Install as follows using the package management tool apk.
Install client
$ apk update
$ apk add mariadb-client
Although it is mariadb, it can be used with MySQL without any problem.
I didn't keep the DB server on localhost, so I installed only the client.
Now you can operate MySQL on the command line by entering the command as follows.
Connection command (Please replace the host name and user name)
$ mysql -h {host} -u {user} -p
MySQLdb is not available and the script cannot access the DBWhen developing a web application in Python I think that you often use SQLAlchemy as an ORM to access the DB. (The exception is Django because it has its own ORM)
According to the Flask-SQLAlchemy documentation (http://flask-sqlalchemy.pocoo.org/2.1/config/#configuration-keys), I initially wrote:
config.py
SQLALCHEMY_DATABASE_URI = 'mysql://username:password@server/db'
An error will occur if you access the DB with this setting.
Error log
$ ImportError: No module named 'MySQLdb'
So I try the installation as below, but it doesn't work.
Installing MySQLdb
$ pip install MySQL-python
Error log
$ ImportError: No module named 'ConfigParser'
A quick look at this point shows that MySQLdb isn't available in Python 3. I've seen some ways to use it in some articles There is no reason to stick to MySQLdb this time, so consider other libraries.
When you look at SQLAlchemy documentation, it looks like [PyMySQL](https: // github.com/PyMySQL/PyMySQL) is the runner-up I chose this because compatibility seems to be no problem (although the reading comprehension in English is ambiguous). I installed and changed the config as follows, and confirmed the normal operation.
Install PyMySQL
$ pip install PyMySQL
config.py
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://username:password@server/db'
If you are using Python, you will be stuck with Japanese processing. This time, the following error occurred when inserting a Japanese character string into the DB.
error
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1366, "Incorrect string value: '\\xE9\\x98\\xBF\\xE4\\xBA\\x95...' for column 'name' at row 1")
The following is specified in the shebang line of the script to be used.
xxx.py
# -*- coding: utf-8 -*-
If there is any other problem, I think it is the character code setting on the MySQL side.
Check the settings on the mysql command line as follows:
Check MySQL settings
MySQL [(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.01 sec)
It's really suspicious that latin1 is mixed.
There is no particular merit as it is, so I would like to change it.
I think the most decent way is to set my.cnf
This time I'm running the MySQL server as a container, so it's awkward to change.
So, I thought about setting this when setting up the container in the first place.
As a result of the survey, the following pages were very helpful. Specify the character code in MySQL of Docker official image Official image of MySQL
Many configuration options can be passed as flags to mysqld. This will give you the flexibility to customize the container without needing a cnf file.
(Japanese translation)
You can pass many options to mysqld as flags.
This gives you the flexibility to customize your container without the need for a cnf file.
Because docker-compose is used in my environment
Add the following description to docker-compose.yml.
docker-compose.yml
command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci
Check the MySQL settings again.
Check MySQL settings
MySQL [(none)]> SHOW VARIABLES LIKE 'chara%';
+--------------------------+----------------------------+
| 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)
character_set_database and character_set_server have changed to ʻutf-8`.
I was able to insert Japanese safely in this state.
There are some problems due to creating a DB server in a container,
Since the environment can be created just by clicking with the docker-compose command
Once you get used to it, you can speed up development.
By the way, I introduced three points that I'm addicted to this time. There is often the possibility that it will increase as you use it in the future.
Recommended Posts