How to setup MySQL server on EC2

Installing MySQL server on EC2 is simple, but if you want to setup a MySQL server for production, it is tedious. You can choose Amazon RDS, Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. Using Amazon RDS can save you a lot of time, but if you still want to setup a MySQL server on EC2 like me, you can continue to read this article.

1. Install MySQL on EC2

Use the yum install command to install MySQL server:

sudo yum install mysql55-server

Start the server:

sudo service mysqld start

Run mysql_secure_installation:

sudo mysql_secure_installation

a. Enter the password for root acount, if the the password is not set, press Enter directly.

b. For the following questions, all type Y.

If you want the MySQL server to start at every boot, enter the following command.

sudo chkconfig mysqld on

2. Change SWAP file size

EC2 default swap file size is 0. After MySQL server runs several days, there may be not enough memory to start already. When you want to start the server, you should see some error messages including:

To solve this issue, you need to add the swap file to EC2.

For example, to add a 1GB swap file, from command line you’ll type:

sudo dd if=/dev/zero of=/swapfile bs=1024 count=1048576

Now setup the swap file with the command:

sudo mkswap /swapfile
sudo chmod 600 /swapfile

Now enable the swap:

sudo swapon /swapfile

If you use the top command, you should now see the 1gb swap added. So now lets make swap persistent so it’s not dropped when you reboot. Edit /etc/fstab file and add this line as the last line:

/swapfile swap swap defaults 0 0

When you reboot, use the free -m or df -h command to check for swap.

3. Change character setting to UTF-8 to support Chinese Character

Change the character setting in MySQL configuration file, so every time the server starts, the character setting is set.

Open the file for editing with superuser, the file only can be edited by superuser.

sudo vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
character-set-server=utf8
collation-server=utf8_general_ci
init-connect=’SET NAMES utf8'
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Add the above 3 commands in red color and restart the server.

sudo service mysqld restart

For the other methods to change character setting, please refer to,

http://dev.mysql.com/doc/refman/5.7/en/charset-applications.html

If your table is using other character set before, you need to drop and create the table again to support new settings.

If you still cannot see the content change to Chinese character, there may be some other reason caused the issue, you can refer to this link to try.

http://blog.itpub.net/26230597/viewspace-1447291/