Archive
Getting MySQL Server Up on a Docker Container
Problem:
I needed mysql-server for a flask tutorial, but I did not want to install it locally on my machine. I opted to give a docker container a shot and get out of my comfort zone of using vagrant boxes.
Solution:
Install the command line mysql client.
$ brew install mysql-client Updating Homebrew... ==> Auto-updated Homebrew! Updated 1 tap (homebrew/core). No changes to formulae. ==> Downloading https://homebrew.bintray.com/bottles/mysql-client-5.7.23.high_sierra.bottle.tar.gz ==> Downloading from https://akamai.bintray.com/8b/8b73614068a82ed9f82f19e52fdd621adcd4d017fc3767703f216cbf7b00e60e?__gda__=exp=1542310178~hmac=5e9 ######################################################################## 100.0% ==> Pouring mysql-client-5.7.23.high_sierra.bottle.tar.gz ==> Caveats mysql-client is keg-only, which means it was not symlinked into /usr/local, because conflicts with mysql. If you need to have mysql-client first in your PATH run: echo 'export PATH="/usr/local/opt/mysql-client/bin:$PATH"' >> ~/.zshrc For compilers to find mysql-client you may need to set: export LDFLAGS="-L/usr/local/opt/mysql-client/lib" export CPPFLAGS="-I/usr/local/opt/mysql-client/include" ==> Summary /usr/local/Cellar/mysql-client/5.7.23: 232 files, 80.5MB $ echo 'export PATH="/usr/local/opt/mysql-client/bin:$PATH"' >> ~/.zshrc
Then
$ docker run --name=test-mysql --env="MYSQL_ROOT_PASSWORD=mypassword" --publish 6603:3306 mysql
In another terminal
$ mysql -u root -p -h 127.0.0.1 -P 6603 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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>
To stop and destroy the containers.
$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES ec35ea89e39b mysql "docker-entrypoint.s…" 16 minutes ago Up 16 minutes 33060/tcp, 0.0.0.0:6603->3306/tcp test-mysql $ docker stop test-mysql test-mysql $ docker rm test-mysql test-mysql $ docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
Source:
https://severalnines.com/blog/mysql-docker-containers-understanding-basics
pip install MySQL-python fails
Problem:
I was trying to install MySQLdb for python on an Ubuntu box (Virtual Machine). But pip fails with below error.
vagrant@vagrant-ubuntu-trusty-64:~$ sudo pip install MySQL-python
Downloading/unpacking MySQL-python
Downloading MySQL-python-1.2.5.zip (108kB): 108kB downloaded
Running setup.py (path:/tmp/pip_build_root/MySQL-python/setup.py) egg_info for package MySQL-python
sh: 1: mysql_config: not found
Traceback (most recent call last):
File "<string>", line 17, in <module>
File "/tmp/pip_build_root/MySQL-python/setup.py", line 17, in <module>
metadata, options = get_config()
File "setup_posix.py", line 43, in get_config
libs = mysql_config("libs_r")
File "setup_posix.py", line 25, in mysql_config
raise EnvironmentError("%s not found" % (mysql_config.path,))
EnvironmentError: mysql_config not found
Complete output from command python setup.py egg_info:
sh: 1: mysql_config: not found
Traceback (most recent call last):
File "<string>", line 17, in <module>
File "/tmp/pip_build_root/MySQL-python/setup.py", line 17, in <module>
metadata, options = get_config()
File "setup_posix.py", line 43, in get_config
libs = mysql_config("libs_r")
File "setup_posix.py", line 25, in mysql_config
raise EnvironmentError("%s not found" % (mysql_config.path,))
EnvironmentError: mysql_config not found
----------------------------------------
Cleaning up...
Command python setup.py egg_info failed with error code 1 in /tmp/pip_build_root/MySQL-python
Storing debug log for failure in /home/vagrant/.pip/pip.log
Solution:
vagrant@vagrant-ubuntu-trusty-64:~$ sudo apt-get install python-dev libmysqlclient-dev vagrant@vagrant-ubuntu-trusty-64:~$ sudo pip install MySQL-python Downloading/unpacking MySQL-python Downloading MySQL-python-1.2.5.zip (108kB): 108kB downloaded Running setup.py (path:/tmp/pip_build_root/MySQL-python/setup.py) egg_info for package MySQL-python Installing collected packages: MySQL-python Running setup.py install for MySQL-python ... ... Successfully installed MySQL-python Cleaning up... vagrant@vagrant-ubuntu-trusty-64:~$ python Python 2.7.6 (default, Jun 22 2015, 17:58:13) [GCC 4.8.2] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> import MySQLdb >>> exit() vagrant@vagrant-ubuntu-trusty-64:~$
Source:
http://stackoverflow.com/questions/25865270/how-to-install-python-mysqldb-module-using-pip
bash – Mimic MySQL GROUP BY function with awk
Problem:
I needed to get GROUP BY results and I did not have the time to wait to restore the mysqldump and run the commands. I had like over 360 zipped files to work with. Restoring each database and running the query even in a script was just too long a process. I opted to just extract the data and use awk to mimic GROUP BY.
Solution:
I used sed to extract the desired results from the mysql dump files. Then took the relevant date fields and used awk to do the group count.
Here is the SELECT…GROUP BY using mysql
$ mysql -u <user> -p<password> -e"SELECT date_format(timestamp,'%Y-%m-%d') as 'Date',count(distinct(<field>)) FROM <table> WHERE <search criteria> GROUP BY date_format(timestamp,'%Y-%m-%d') ORDER BY date_format(timestamp,'%Y-%m-%d')" | sed 's/\t/,/g' 2012-10-10,4 2012-10-11,21 2012-10-12,19 2012-10-13,8 2012-10-14,22 2012-10-15,7 2012-10-16,13
Here is the same but now using awk. After extracting the data from the dumps I end up with the following
$ head -n 3 <file-with-extracted-content>
2012-10-01 06:08:54|377772327281
2012-10-01 06:08:55|371202225977
2012-10-01 06:08:55|061712877272
$
$ awk -v FS=" |\|" '{print $1, $3}' <file-with-extracted-content> |
sort -u |
awk -v OFS=, '{a[$1]+=1}END{for (i in a) print i,a[i]}' |
sort -t, -k1
2012-10-10,4
2012-10-11,21
2012-10-12,19
2012-10-13,8
2012-10-14,22
2012-10-15,7
MySQL – Moving the datadir
Problem:
My default mysql location is /var/lib/mysql and I needed to restore a database that takes up a few GB of data. My root partition where /var is does not have enough space.
Solution:
Move the location of mysql’s datadir. Follow the steps below. (I had upgraded mysql and had made some changes as noted in this earlier post)
1. Stop mysql
root@computer:/home/user# /etc/init.d/mysql stop Stopping MySQL database server: mysqld.
2. Edit my.cnf
In my case I had renamed it to some thing else. So I put the file back.
root@computer:/home/user# cd /etc/mysql root@computer:/etc/mysql# cp my.cnf.issues my.cnf
Now edit the line
from
datadir = /var/lib/mysql
to
datadir = /home/user/mysqldatadir
3. Copy the contents of /var/lib/mysql to the new location /home/user/mysqldatadir.
I did this a few times so I am guessing that you only need to copy the folder that has the default mysql database. The one that normally shows up when you have a fresh mysql install. As shown below.
mysq> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ So root@computer# cd /var/lib/mysql root@computer:/var/lib/mysql# ls debian-5.1.flag ibdata1 ib_logfile0 ib_logfile1 mysql mysql_upgrade_info carsdb
Now copy mysql folder to the new datadir location.
Also copy mysql_upgrade_info file.
Try and preserve the file permissions. The other files ie ibdata1 and ib_logfile* will be created. the folder carsdb is database that I do not need moved. (I intend to copy that over by first dumping the data using mysqldump.)
root@computer:/var/lib/mysql# cp -prv mysql /home/user/mysqldatadir/ root@computer:/var/lib/mysql# cp -prv mysql_upgrade_info /home/user/mysqldatadir/
4. Now change the permissions of the new datadir to belong to mysql
root@computer:/home/user# chown mysql:mysql mysqldatadir/
5. Start up mysql
root@computer:/var/lib/mysql# cd /home/user/ root@computer:/home/user# /etc/init.d/mysql start Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. root@computer:/home/user# ls -al mysqldatadir/ drwxrwxrwx 5 mysql mysql 4096 Oct 26 16:25 . drwxr-xr-x 81 user user 4096 Oct 26 16:24 .. -rw-rw---- 1 mysql mysql 10485760 Oct 26 16:24 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Oct 26 16:24 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Oct 26 15:54 ib_logfile1 drwx------ 2 mysql mysql 4096 Jun 22 15:55 mysql -rw------- 1 mysql mysql 6 Jun 22 15:55 mysql_upgrade_info
6. Nothing else should change. So your previous users are still valid.
user@computer:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 57 Server version: 5.1.63-0+squeeze1 (Debian) Copyright (c) 2000, 2011, 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>
7. If mysql fails to start have a look at the syslog. Should be near the end of the file.
root@computer:/home/user# less /var/log/syslog
Source:
http://rajshekhar.net/blog/archives/90-Moving-the-MySQLs-datadir-directory..html
MySQL – ERROR 1030 (HY000): Got error 28 from storage engine
Problem:
Got this error when trying to restore a database.
ERROR 1030 (HY000): Got error 28 from storage engine
Solution:
Turns out that I was running out of space on /var partition where mysql is. The database I was restoring takes up about 3 GB, 1.1 GB was not enough. So the restore starts but does not end.
user@computer$ df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 19G 3.1G 15G 18% / tmpfs 2.0G 0 2.0G 0% /lib/init/rw udev 10M 64K 10M 1% /dev tmpfs 2.0G 0 2.0G 0% /dev/shm /dev/sda5 9.2G 4.2G 4.6G 48% /home /dev/sda7 100G 94G 1.1G 99% /var
Just needed to free up space. Fortunately, I was able to delete the mysql bin longs that were taking up almost 80GB of space.
user@computer$ df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 19G 3.1G 15G 18% / tmpfs 2.0G 0 2.0G 0% /lib/init/rw udev 10M 64K 10M 1% /dev tmpfs 2.0G 0 2.0G 0% /dev/shm /dev/sda5 9.2G 4.2G 4.6G 48% /home /dev/sda7 100G 13G 83G 14% /var
Once that was done, I was able to restore the database.
Source:
https://forums.mysql.com/read.php?35,84004,224693#msg-224693
MySQL – Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
Problem:
I upgraded mysql server from 5.1 to 5.1.6 and when I try to log in I get the following error.
user@computer:~$ mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Solution:
After a quick search on google this is what helped me.
1. Completely remove mysqlserver-5.1.6
2. I renamed the existing /etc/mysql/my.cnf to /etc/mysql/my.cnf.doesnotwork.
3. Install mysqlserver-5.1.6 again.
MySQL Error – Operand should contain 1 column(s)
Problem:
I was getting the following mysql error in python.
OperationalError: (1241, ‘Operand should contain 1 column(s)’)
Solution:
Convert the python list to a string.
Here is what I was not doing.
>>>my_list ['First', 'Second', 'Third']
Instead of
INSERT INTO table (data) VALUES (str(my_list))
I was doing this
INSERT INTO table (data) VALUES (my_list)
Source:
This helped narrow down the issue.
http://lists.mysql.com/mysql/189034
MySQL – Remove a character from a string
Problem:
Had to strip out a plus sign at the beginning of some text in the database.
Solution:
Update command and replace function in MySQL. In this case I was getting rid of the plus sign.
UPDATE `table` SET db_field=REPLACE(db_field, ‘+’, ”) where db_field like ‘+3989%’ limit 5;
Source:
http://eisabainyo.net/weblog/2007/02/26/remove-the-first-character-in-mysql/
Error 2006: MySQL Server has gone away.
Problem: Getting this error in my python script
Traceback (most recent call last):
File “my_application.py”, line 202, in
main()
File “my_application.py”, line 186, in main
traceback.format_exec())
AttributeError: ‘module’ object has no attribute ‘format_exec’
When I check my application logs I have
OperationalError: (2006, ‘MySQL server has gone away’)
Solution:
Increase the wait_timeout value in /etc/mysql/my.cnf.
In my case I changed it from 6 to 600
wait_timeout = 600
As root restart the mysql server. /etc/init.d/mysql restart
More information on this error (for mysql 5.0) can be found here. http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
Killing MySQL queries
Problem: Opps query on a table is taking too long. Due to
a) Lack of index
b) Forgot to put a limit.
The table in question has over 20Million rows.
Solution:
1. Connect to mysql as your given users eg guest
mysql> show process list;
+———–+———+——————+——–+———+——-+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+———–+———+——————+——–+———+——-+——-+——————+
| 120 | guest | localhost:57031 | database1 | Query | 0 | NULL | show processlist |
| 156 | guest | localhost:52753 |database1 | Query | 40 | Copying to tmp table | SELECT * FROM table1 INNER JOI |
| 192 | guest | localhost:54919 | database1 | Sleep | 21491 | | NULL |
+———–+———+——————+——–+———+——-+——-+——————+
2. Identify the query you would like to stop.
mysql>kill 156;
3. Check to see that the process has gone away.
mysql> show processlist;
+———–+———+——————+——–+———+——-+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+———–+———+——————+——–+———+——-+——-+——————+
| 120 | guest | localhost:57031 | database1 | Query | 0 | NULL | show processlist |
| 192 | guest | localhost:54919 | database1 | Sleep | 21491 | | NULL |
+———–+———+——————+——–+———+——-+——-+——————+
The documentation for the KILL command is here http://dev.mysql.com/doc/refman/5.0/en/kill.html.
pseudo-scripter 