Amazon Web Services - AWS EC2 - Ubuntu 14.04

MySQL Installation and Configuration

1. Installation

#apt-add-repository ppa:ondrej/mysql-5.6

#apt-get update

#apt-get install mysql-server mysql-client

... installation process

# mysql --version

mysql  Ver 14.14 Distrib 5.6.24, for debian-linux-gnu (x86_64) using  EditLine wrapper

 

2. Starting

# service mysql start | restart | stop | status

 

2.1 Config file: /etc/mysql/my.cnf

Enabling UTF-8 characters

Edit /etc/mysql/my.cnf file and add red lines

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake

#service mysql restart

Check it by:

#mysql -p

> SHOW VARIABLES LIKE 'character_set%';

Output should be:

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/

 

 

 

3. MySQL client - command line interface

# mysql -h localhost -u root -ppassword

or just

# mysql -p

 

Commands:

Show all commands >help.

 

>SHOW DATABASES;

>USE baza;

>SHOW TABLES;  

>SELECT * FROM  user

 

- add new user

>GRANT ALL on tablename.* TO click_user@localhost IDENTIFIED BY 'pass'

 

-modify user's password

>UPDATE mysql.user SET Password=PASSWORD('pmD532!') WHERE User='phpmyadmin' AND Host='localhost';

>FLUSH PRIVILEGES;

 

- delete user

>drop user click_user@localhost

 

4. MySQL Logs

Errors: # more /var/log/mysql.err

 

To reciord each MySQL query  /etc/mysql/my.cnf dekomentirati linije:

 

#general_log_file        = /var/log/mysql/mysql.log

#general_log             = 1



#log_slow_queries    = /var/log/mysql/mysql-slow.log (bilježi izuzetno spore mysql upite)

 

Modify in logrotate file /etc/logrotate.d/mysql-server       /var/log/mysql/mysql.log and  /var/log/mysql/mysql-slow.log .

 

 

5. MYSQL Fulltext search

File: /etc/mysql/my.cnf

[mysqld]

ft_min_word_len=3

 

Minimum number of characters for MATCH-AGAINST query.

After that delete and create new fullindex in phpMyAdmin.

 

 

 

6. MySQL EXPORT - IMPORT data

 

  1. a) Export on remote server:

#mysqldump -u USER -pPASSWORD DB_NAME > db_name.sql

#gzip db_name.sgl   -> kreira db_name.sql.gz koji je čak 10x manji

 

  1. b) Wget transfer to our server

#wget -nH

 

  1. c) import

mysql> use DATABASE_NAME;

mysql> source path/to/file.sql;

ako je file.sql.gz onda prvo napraviti

#gunzip file.sql.gz          --da se makne .gz extenzija

#gunzip -c 1click_ads_with_data.sql.gz > 1click_ads_with_data.sql       --zadržava gz file

 

 

7. Connect remotelly to another MYSQL server

  1. a) Prvo je potrebno komentirati liniju u /etc/mysql/my.cnf tako da bude

#bind-address        = 127.0.0.1

 

  1. b) Nakon toga dodati usera za sve vanjske hostove

mysql> GRANT ALL ON base_name.* TO mysql_user@'%' IDENTIFIED BY 'password';

 

Napomena: % znači da će prihvaćati konekcije sa svih IP-ova.

Ako želimo ograničiti pristup samo jednom IP-u onda treba:

mysql> GRANT ALL ON base_name.* TO mysql_user@'95.178.138.237' IDENTIFIED BY 'password';

 

  1. c) Restartati mysql

#service mysql restart

 

d) Sada se možemo sa našeg računala konektirati na udaljeni server. Udaljenom serveru je IP adresa npr. 84.241.137.166

#mysql -u mysql_user -h 84.241.137.166 -p