Difference between revisions of "Centos: config mysql server"

From cslt Wiki
Jump to: navigation, search
 
(16 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
1. install mysql server on centos
 
1. install mysql server on centos
  a) wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
+
  a) wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
  b) rpm -ivh mysql-community-release-el7-5.noarch.rpm
+
  b) rpm -ivh mysql57-community-release-el7-7.noarch.rpm
 
  c) yum install mysql-community-server
 
  c) yum install mysql-community-server
 
  d) service mysqld restart
 
  d) service mysqld restart
  
 
2. set password for 'root'
 
2. set password for 'root'
  a) # mysql -u root
+
  a) # vi /etc/my.cnf
  b) mysql> use mysql;
+
    add skip-grant-tables in [mysqld]:
  c) mysql> update user set password=PASSWORD(‘your_password’) where User='root';
+
    [mysqld]
 +
    datadir=/var/lib/mysql  
 +
    socket=/var/lib/mysql/mysql.sock
 +
    skip-grant-tables
 +
  b) # service mysqld restart
 +
c) # mysql
 +
d) mysql> use mysql;
 +
  e) mysql> update user set authentication_string=PASSWORD(‘your_password’) where User='root';
 +
f) comment skip-grant-tables in /etc/my.cnf then restart the service
  
 
3. add new user
 
3. add new user
Line 33: Line 41:
 
7. change the datadir if you want
 
7. change the datadir if you want
 
  a) stop mysql
 
  a) stop mysql
* systemctl stop mysqld.service
+
    1) systemctl stop mysqld.service
* create new mysql data directory
+
b) create new mysql data directory
 +
    1) mkdir /var/data/mysql
 +
c) 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
 +
d) copy all files from /var/lib/mysql to the new directory /var/data/mysql
 +
    1) cp -r /var/lib/mysql/* /var/data/mysql
 +
e) permissions for the new directory
 +
    1) chown -R mysql /var/data/mysql;
 +
    2) chgrp -R mysql /var/data/mysql;
 +
    3) chmod -R g+rw /var/data/mysql;
 +
f) also modify SELINUX settings to allow mysql to use the different path
 +
    # add context and make it permanent
 +
    1) semanage fcontext -a -s system_u -t mysqld_db_t "/var/data/mysql(/.*)?"
 +
    2) restorecon -Rv /var/data/mysql
 +
g) start mysql
 +
    1) systemctl start mysqld.service
  
mkdir /var/data/mysql
+
8. install phpmyadmin
modify /etc/my.cnf and point to new data directory – add the client section to the top
+
a) git clone https://github.com/phpmyadmin/phpmyadmin.git into the root directory of your web site
 +
b) # cp /var/www/html/phpmyadmin/libraries/config.default.php \
 +
                /var/www/html/phpmyadmin/config.inc.php
 +
c)  # vim /var/www/html/phpmyadmin/config.inc.php
 +
                    $cfg['blowfish_secret'] = ''; ==> \
 +
                    $cfg['blowfish_secret'] = 'your secret  password';
 +
                    $cfg['Servers'][$i]['auth_type'] = 'config'; ==> \
 +
                    $cfg['Servers'][$i]['auth_type'] = 'cookie';
 +
                    $cfg['Servers'][$i]['user'] = ''; ==> $cfg['Servers'][$i]['user'] = 'root';
 +
                    $cfg['Servers'][$i]['password'] = ''; ==> \
 +
                    $cfg['Servers'][$i]['password'] = 'your mysql password';
 +
d) # systemctl restart mysqld
 +
e) # systemctl restart httpd
  
[client]
+
Issues:
port=3306
+
a) 1146 - Table 'data_dictionary.CHARACTER_SETS' doesn't exist
socket=/var/data/mysql/mysql.sock
+
    solution: mysql_upgrade -u root -p --force --upgrade-system-tables
 
+
b) 1290, 'The MySQL server is running with the --secure-file-priv option so it cannot execute this statement'
[mysqld]
+
    solution: add secure-file-priv = "" to my.cnf
datadir=/var/data/mysql
+
c) mysql 中文乱码
socket=/var/data/mysql/mysql.sock
+
    > show variables like 'character%'; 查看编码
copy all files from /var/lib/mysql to the new directory /var/data/mysql
+
    solution: modify /etc/my.cnf
 
+
    [client]
cp -r /var/lib/mysql/* /var/data/mysql
+
    default-character-set = utf8
permissions for the new directory
+
    [mysql]
 +
    default-character-set = utf8
 +
    [mysqld]
 +
    character-set-server=utf8
 +
    collation-server=utf8_general_ci

Latest revision as of 12:21, 24 January 2018

1. install mysql server on centos

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

2. set password for 'root'

a) # vi /etc/my.cnf 
   add skip-grant-tables in [mysqld]:
   [mysqld] 
   datadir=/var/lib/mysql 
   socket=/var/lib/mysql/mysql.sock 
   skip-grant-tables 
b) # service mysqld restart
c) # mysql
d) mysql> use mysql;
e) mysql> update user set authentication_string=PASSWORD(‘your_password’) where User='root';
f) comment skip-grant-tables in /etc/my.cnf then restart the service

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
   1) systemctl stop mysqld.service
b) create new mysql data directory
   1) mkdir /var/data/mysql
c) 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
d) copy all files from /var/lib/mysql to the new directory /var/data/mysql
   1) cp -r /var/lib/mysql/* /var/data/mysql
e) permissions for the new directory
   1) chown -R mysql /var/data/mysql;
   2) chgrp -R mysql /var/data/mysql;
   3) chmod -R g+rw /var/data/mysql;
f) also modify SELINUX settings to allow mysql to use the different path
   # add context and make it permanent 
   1) semanage fcontext -a -s system_u -t mysqld_db_t "/var/data/mysql(/.*)?"
   2) restorecon -Rv /var/data/mysql
g) start mysql
   1) systemctl start mysqld.service

8. install phpmyadmin

a) git clone https://github.com/phpmyadmin/phpmyadmin.git into the root directory of your web site
b) # cp /var/www/html/phpmyadmin/libraries/config.default.php \
               /var/www/html/phpmyadmin/config.inc.php
c)  # vim /var/www/html/phpmyadmin/config.inc.php
                   $cfg['blowfish_secret'] = ; ==> \
                   $cfg['blowfish_secret'] = 'your secret  password'; 
                   $cfg['Servers'][$i]['auth_type'] = 'config'; ==> \
                   $cfg['Servers'][$i]['auth_type'] = 'cookie';
                   $cfg['Servers'][$i]['user'] = ; ==> $cfg['Servers'][$i]['user'] = 'root';
                   $cfg['Servers'][$i]['password'] = ; ==> \
                   $cfg['Servers'][$i]['password'] = 'your mysql password';
d) # systemctl restart mysqld
e) # systemctl restart httpd

Issues:

a) 1146 - Table 'data_dictionary.CHARACTER_SETS' doesn't exist
    solution: mysql_upgrade -u root -p --force --upgrade-system-tables
b) 1290, 'The MySQL server is running with the --secure-file-priv option so it cannot execute this statement'
    solution: add secure-file-priv = "" to my.cnf
c) mysql 中文乱码
   > show variables like 'character%'; 查看编码
   solution: modify /etc/my.cnf
    [client]
    default-character-set = utf8
    [mysql]
    default-character-set = utf8
    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci