Difference between revisions of "Centos: config mysql server"

From cslt Wiki
Jump to: navigation, search
Line 30: Line 30:
 
6. try to access mysql from remote host
 
6. try to access mysql from remote host
 
  a) mysql -h "xxx.xxx.xxx.xxx" -u username -p
 
  a) mysql -h "xxx.xxx.xxx.xxx" -u username -p
 +
 +
7. change the datadir if you want
 +
a) stop mysql
 +
* systemctl stop mysqld.service
 +
* create new mysql data directory
 +
 +
mkdir /var/data/mysql
 +
modify /etc/my.cnf and point to new data directory – add the client section to the top
 +
 +
[client]
 +
port=3306
 +
socket=/var/data/mysql/mysql.sock
 +
 +
[mysqld]
 +
datadir=/var/data/mysql
 +
socket=/var/data/mysql/mysql.sock
 +
copy all files from /var/lib/mysql to the new directory /var/data/mysql
 +
 +
cp -r /var/lib/mysql/* /var/data/mysql
 +
permissions for the new directory

Revision as of 16:53, 1 November 2016

1. install mysql server on centos

a) wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
b) rpm -ivh mysql-community-release-el7-5.noarch.rpm
c) yum install mysql-community-server
d) service mysqld restart

2. set password for 'root'

a) # mysql -u root
b) mysql> use mysql;
c) mysql> update user set password=PASSWORD(‘your_password’) where User='root';

3. add new user

a) create user 'username'@'xxx.xxx.xxx.xxx' identified by 'password'; ('%' match any ip address)
a) mysql> select host,user from user; (check the privileges)

4. grant privileges for the user

a) grant all privileges on stock to 'username'@'xxx.xxx.xxx.xxx'; (grant privileges of database stock to 'username'@'xxx.xxx.xxx.xxx. *.* match any database)
b) select host,user from user; (check the privileges) 

5. allow firewall accept port 3306

a) # firewall-cmd --add-service=mysql --permanent
    success
b) # firewall-cmd --reload
    success
c) iptables -L -n | grep 3306 (check whether port 3306 is accept)
    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:3306 ctstate NEW
d) firewall-cmd --state (check the state of the firewall)
     running

6. try to access mysql from remote host

a) mysql -h "xxx.xxx.xxx.xxx" -u username -p

7. change the datadir if you want

a) stop mysql
* systemctl stop mysqld.service
* create new mysql data directory

mkdir /var/data/mysql modify /etc/my.cnf and point to new data directory – add the client section to the top

[client] port=3306 socket=/var/data/mysql/mysql.sock

[mysqld] datadir=/var/data/mysql socket=/var/data/mysql/mysql.sock copy all files from /var/lib/mysql to the new directory /var/data/mysql

cp -r /var/lib/mysql/* /var/data/mysql permissions for the new directory