MySQL

Install MySQL in Mac

1. Installation and set up

$ brew install mysql
$ sudo ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

1. Service management

sudo /usr/local/mysql/support-files/mysql.server start/restart/stop

1. Change Root password

(1). Check for version
> select version()
(2). For MySQL 5.7.6 and later:
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
(3). Earlier version
> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');

Change MySQL encoding

1. First you need to stop mysql service

$ sudo service mysql stop

1. Change the my.conf file

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

1. start mysql server

$ sudo service mysql start
Verify the settings works:
$ show variables like "%character%";show variables like "%collation%";

Connect python with MySQL

1. Specify the encoding method in the script:

#!/usr/bin/python
import MySQLdb

# connect
db = MySQLdb.connect(host="localhost", user="appuser", passwd="",
db="onco", init_command="set names utf8")

cursor = db.cursor()

# execute SQL select statement
cursor.execute("SELECT * FROM LOCATION")

# commit your changes
db.commit()

# get the number of rows in the resultset
numrows = int(cursor.rowcount)

# get and display one row at a time.
for x in range(0,numrows):
    row = cursor.fetchone()
    print row[0], "-->", row[1]

Mysql installation

1. Where to find the initial password
cat /var/log/mysqld.log

Connect mysql with different port

mysql -u root -h 127.0.0.1 -p --port=3308