Difference between revisions of "MySQL server"
(Created page with "===Installation=== ====Required packages==== <syntaxhighlight lang="bash"> apt-get install mysql-server mysql-client </syntaxhighlight> Your database will use InnoDB instead...") |
|||
| (4 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| + | [[Category:Linux]] | ||
| + | [[Category:Development]] | ||
| + | |||
===Installation=== | ===Installation=== | ||
| Line 13: | Line 16: | ||
Stop the service (you cannot configure a running service) | Stop the service (you cannot configure a running service) | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| + | service mysql stop | ||
/etc/init.d/mysql stop | /etc/init.d/mysql stop | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 18: | Line 22: | ||
Edit configuration file | Edit configuration file | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| + | # mysql 5.6+ | ||
| + | vim /etc/mysql/mysql.conf.d/mysqld.cnf | ||
| + | |||
| + | # Old version (mysql up to 5.5) | ||
vim /etc/mysql/my.cnf | vim /etc/mysql/my.cnf | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 29: | Line 37: | ||
Restart service | Restart service | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| − | + | service mysql restart | |
| + | /etc/init.d/mysql restart | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 44: | Line 53: | ||
===Enable remote access=== | ===Enable remote access=== | ||
| + | |||
====Server connection==== | ====Server connection==== | ||
| + | |||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
mysql -u root -p | mysql -u root -p | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | * MySQL 5.6 | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | mysql -u root -p | ||
| + | mysql> use mysql; | ||
| + | mysql> select user,host,authentication_string from user; | ||
| + | mysql> update user set host="%" where user="root" and host="localhost"; | ||
| + | ♠ where 'vks11447' is the server name. | ||
| + | |||
| + | mysql> flush privileges; | ||
| + | mysql> quit; | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | |||
| + | * MySQL 5.5 | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 60: | Line 89: | ||
mysql> quit; | mysql> quit; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| + | |||
Restart server | Restart server | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| + | service mysql restart | ||
/etc/init.d/mysql restart | /etc/init.d/mysql restart | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| + | |||
====Open your firewall==== | ====Open your firewall==== | ||
| + | |||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
# MySQL server | # MySQL server | ||
| Line 82: | Line 115: | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
mysql > use mysql ; | mysql > use mysql ; | ||
| − | mysql > select User,Host,Password from user ; | + | mysql > select User,Host,Password from user; |
</syntaxhighlight> | </syntaxhighlight> | ||
====Change root login==== | ====Change root login==== | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| − | mysql > UPDATE user SET user = | + | mysql > UPDATE user SET user.User = 'admin' WHERE user = 'root'; |
</syntaxhighlight> | </syntaxhighlight> | ||
====Change root password==== | ====Change root password==== | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| − | mysql > | + | mysql > UPDATE user SET user.Password = password('****') WHERE user = 'admin'; |
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 99: | Line 132: | ||
mysql > flush privileges ; | mysql > flush privileges ; | ||
mysql > select User,Host,Password from user ; | mysql > select User,Host,Password from user ; | ||
| − | mysql > exit | + | mysql > exit; |
</syntaxhighlight> | </syntaxhighlight> | ||
| − | + | ====Check results==== | |
| − | + | <syntaxhighlight lang="bash"> | |
| − | + | mysql –u admin –p | |
| − | + | </syntaxhighlight> | |
| − | |||
| − | |||
| − | |||
Latest revision as of 21:55, 21 January 2017
Contents
Installation
Required packages
apt-get install mysql-server mysql-clientYour database will use InnoDB instead of the old, deprecated, database file system.
You’ll have to choose a password for MySQL admin [root] user
Configuration
Stop the service (you cannot configure a running service)
service mysql stop
/etc/init.d/mysql stopEdit configuration file
# mysql 5.6+
vim /etc/mysql/mysql.conf.d/mysqld.cnf
# Old version (mysql up to 5.5)
vim /etc/mysql/my.cnfEdit the file:
[mysqld]
#bind-address = 127.0.0.1 # Comment this line to enable remote accessRestart service
service mysql restart
/etc/init.d/mysql restartCheck running service
netstat -tapyou should have something like:
tcp 0 0 *:mysql *:* LISTEN 3281/mysqld
Enable remote access
Server connection
mysql -u root -p
- MySQL 5.6
mysql -u root -p
mysql> use mysql;
mysql> select user,host,authentication_string from user;
mysql> update user set host="%" where user="root" and host="localhost";
♠ where 'vks11447' is the server name.
mysql> flush privileges;
mysql> quit;
- MySQL 5.5
mysql> use mysql;
mysql> select user,host,password from user;
mysql> update user set host="%" where user="root" and host="vks11447";♠ where 'vks11447' is the server name.
mysql> flush privileges;
mysql> quit;
Restart server
service mysql restart
/etc/init.d/mysql restart
Open your firewall
# MySQL server
$IPTABLES -t filter -A INPUT -p tcp -m state --state NEW --dport 3306 -j ACCEPT
Change root login
Change root login / password
mysql -u root -pDisplay all users
mysql > use mysql ;
mysql > select User,Host,Password from user;Change root login
mysql > UPDATE user SET user.User = 'admin' WHERE user = 'root';Change root password
mysql > UPDATE user SET user.Password = password('****') WHERE user = 'admin';Apply changes
mysql > flush privileges ;
mysql > select User,Host,Password from user ;
mysql > exit;Check results
mysql –u admin –p
