Bureaucrats, dave_user, Administrators
4,650
edits
Changes
→Installing MySQL server with remote access
The configuration file <code>/etc/ssh/sshd_config</code> is set to permit <code>root</code> user to log in.
===Installing MySQL server with remote access===
This section shows how to install MySQL server and how to enable remote access for <code>root</code> user.
{{ImportantMessage|text=From a security standpoint, giving <code>root</code> user logon permission from any host may be dangerous.
}}
Firstly, install the <code>mysql-server</code> package. During the installation process, you will set the password for the <code>root</code> user:
<pre class="board-terminal">
root@arm:~# sudo apt-get update
Get:1 http://security.debian.org jessie/updates InRelease [63.1 kB]
Processing triggers for systemd (230-7~bpo8+2) ...
root@arm:~#
</pre>
Verify the server is up and running:
<pre class="board-terminal">
root@arm:~# mysql -u root -p
Enter password:
Bye
root@arm:~#
</pre>
Edit the <code>/etc/mysql/my.cnf</code> file in order to comment out the following lines:<pre class="board-terminal">
#bind-address = 127.0.0.1
#skip-networking
</pre>
Restart the server to make the changes take effect:
<pre class="board-terminal">
root@arm:~# service mysql restart
</pre>
Access MySQL server locally and issue the commands shown below:
<pre class="board-terminal">
root@arm:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.54-0+deb8u1 (Debian)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * from information_schema.user_privileges where grantee like "'root'%";
+--------------------+---------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+---------------+-------------------------+--------------+
| 'root'@'localhost' | def | SELECT | YES |
| 'root'@'localhost' | def | INSERT | YES |
| 'root'@'localhost' | def | UPDATE | YES |
| 'root'@'localhost' | def | DELETE | YES |
| 'root'@'localhost' | def | CREATE | YES |
| 'root'@'localhost' | def | DROP | YES |
| 'root'@'localhost' | def | RELOAD | YES |
| 'root'@'localhost' | def | SHUTDOWN | YES |
| 'root'@'localhost' | def | PROCESS | YES |
| 'root'@'localhost' | def | FILE | YES |
| 'root'@'localhost' | def | REFERENCES | YES |
| 'root'@'localhost' | def | INDEX | YES |
| 'root'@'localhost' | def | ALTER | YES |
| 'root'@'localhost' | def | SHOW DATABASES | YES |
| 'root'@'localhost' | def | SUPER | YES |
| 'root'@'localhost' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'localhost' | def | LOCK TABLES | YES |
| 'root'@'localhost' | def | EXECUTE | YES |
| 'root'@'localhost' | def | REPLICATION SLAVE | YES |
| 'root'@'localhost' | def | REPLICATION CLIENT | YES |
| 'root'@'localhost' | def | CREATE VIEW | YES |
| 'root'@'localhost' | def | SHOW VIEW | YES |
| 'root'@'localhost' | def | CREATE ROUTINE | YES |
| 'root'@'localhost' | def | ALTER ROUTINE | YES |
| 'root'@'localhost' | def | CREATE USER | YES |
| 'root'@'localhost' | def | EVENT | YES |
| 'root'@'localhost' | def | TRIGGER | YES |
| 'root'@'localhost' | def | CREATE TABLESPACE | YES |
| 'root'@'arm' | def | SELECT | YES |
| 'root'@'arm' | def | INSERT | YES |
| 'root'@'arm' | def | UPDATE | YES |
| 'root'@'arm' | def | DELETE | YES |
| 'root'@'arm' | def | CREATE | YES |
| 'root'@'arm' | def | DROP | YES |
| 'root'@'arm' | def | RELOAD | YES |
| 'root'@'arm' | def | SHUTDOWN | YES |
| 'root'@'arm' | def | PROCESS | YES |
| 'root'@'arm' | def | FILE | YES |
| 'root'@'arm' | def | REFERENCES | YES |
| 'root'@'arm' | def | INDEX | YES |
| 'root'@'arm' | def | ALTER | YES |
| 'root'@'arm' | def | SHOW DATABASES | YES |
| 'root'@'arm' | def | SUPER | YES |
| 'root'@'arm' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'arm' | def | LOCK TABLES | YES |
| 'root'@'arm' | def | EXECUTE | YES |
| 'root'@'arm' | def | REPLICATION SLAVE | YES |
| 'root'@'arm' | def | REPLICATION CLIENT | YES |
| 'root'@'arm' | def | CREATE VIEW | YES |
| 'root'@'arm' | def | SHOW VIEW | YES |
| 'root'@'arm' | def | CREATE ROUTINE | YES |
| 'root'@'arm' | def | ALTER ROUTINE | YES |
| 'root'@'arm' | def | CREATE USER | YES |
| 'root'@'arm' | def | EVENT | YES |
| 'root'@'arm' | def | TRIGGER | YES |
| 'root'@'arm' | def | CREATE TABLESPACE | YES |
| 'root'@'127.0.0.1' | def | SELECT | YES |
| 'root'@'127.0.0.1' | def | INSERT | YES |
| 'root'@'127.0.0.1' | def | UPDATE | YES |
| 'root'@'127.0.0.1' | def | DELETE | YES |
| 'root'@'127.0.0.1' | def | CREATE | YES |
| 'root'@'127.0.0.1' | def | DROP | YES |
| 'root'@'127.0.0.1' | def | RELOAD | YES |
| 'root'@'127.0.0.1' | def | SHUTDOWN | YES |
| 'root'@'127.0.0.1' | def | PROCESS | YES |
| 'root'@'127.0.0.1' | def | FILE | YES |
| 'root'@'127.0.0.1' | def | REFERENCES | YES |
| 'root'@'127.0.0.1' | def | INDEX | YES |
| 'root'@'127.0.0.1' | def | ALTER | YES |
| 'root'@'127.0.0.1' | def | SHOW DATABASES | YES |
| 'root'@'127.0.0.1' | def | SUPER | YES |
| 'root'@'127.0.0.1' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'127.0.0.1' | def | LOCK TABLES | YES |
| 'root'@'127.0.0.1' | def | EXECUTE | YES |
| 'root'@'127.0.0.1' | def | REPLICATION SLAVE | YES |
| 'root'@'127.0.0.1' | def | REPLICATION CLIENT | YES |
| 'root'@'127.0.0.1' | def | CREATE VIEW | YES |
| 'root'@'127.0.0.1' | def | SHOW VIEW | YES |
| 'root'@'127.0.0.1' | def | CREATE ROUTINE | YES |
| 'root'@'127.0.0.1' | def | ALTER ROUTINE | YES |
| 'root'@'127.0.0.1' | def | CREATE USER | YES |
| 'root'@'127.0.0.1' | def | EVENT | YES |
| 'root'@'127.0.0.1' | def | TRIGGER | YES |
| 'root'@'127.0.0.1' | def | CREATE TABLESPACE | YES |
| 'root'@'::1' | def | SELECT | YES |
| 'root'@'::1' | def | INSERT | YES |
| 'root'@'::1' | def | UPDATE | YES |
| 'root'@'::1' | def | DELETE | YES |
| 'root'@'::1' | def | CREATE | YES |
| 'root'@'::1' | def | DROP | YES |
| 'root'@'::1' | def | RELOAD | YES |
| 'root'@'::1' | def | SHUTDOWN | YES |
| 'root'@'::1' | def | PROCESS | YES |
| 'root'@'::1' | def | FILE | YES |
| 'root'@'::1' | def | REFERENCES | YES |
| 'root'@'::1' | def | INDEX | YES |
| 'root'@'::1' | def | ALTER | YES |
| 'root'@'::1' | def | SHOW DATABASES | YES |
| 'root'@'::1' | def | SUPER | YES |
| 'root'@'::1' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'::1' | def | LOCK TABLES | YES |
| 'root'@'::1' | def | EXECUTE | YES |
| 'root'@'::1' | def | REPLICATION SLAVE | YES |
| 'root'@'::1' | def | REPLICATION CLIENT | YES |
| 'root'@'::1' | def | CREATE VIEW | YES |
| 'root'@'::1' | def | SHOW VIEW | YES |
| 'root'@'::1' | def | CREATE ROUTINE | YES |
| 'root'@'::1' | def | ALTER ROUTINE | YES |
| 'root'@'::1' | def | CREATE USER | YES |
| 'root'@'::1' | def | EVENT | YES |
| 'root'@'::1' | def | TRIGGER | YES |
| 'root'@'::1' | def | CREATE TABLESPACE | YES |
| 'root'@'%' | def | SELECT | YES |
| 'root'@'%' | def | INSERT | YES |
| 'root'@'%' | def | UPDATE | YES |
| 'root'@'%' | def | DELETE | YES |
| 'root'@'%' | def | CREATE | YES |
| 'root'@'%' | def | DROP | YES |
| 'root'@'%' | def | RELOAD | YES |
| 'root'@'%' | def | SHUTDOWN | YES |
| 'root'@'%' | def | PROCESS | YES |
| 'root'@'%' | def | FILE | YES |
| 'root'@'%' | def | REFERENCES | YES |
| 'root'@'%' | def | INDEX | YES |
| 'root'@'%' | def | ALTER | YES |
| 'root'@'%' | def | SHOW DATABASES | YES |
| 'root'@'%' | def | SUPER | YES |
| 'root'@'%' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'%' | def | LOCK TABLES | YES |
| 'root'@'%' | def | EXECUTE | YES |
| 'root'@'%' | def | REPLICATION SLAVE | YES |
| 'root'@'%' | def | REPLICATION CLIENT | YES |
| 'root'@'%' | def | CREATE VIEW | YES |
| 'root'@'%' | def | SHOW VIEW | YES |
| 'root'@'%' | def | CREATE ROUTINE | YES |
| 'root'@'%' | def | ALTER ROUTINE | YES |
| 'root'@'%' | def | CREATE USER | YES |
| 'root'@'%' | def | EVENT | YES |
| 'root'@'%' | def | TRIGGER | YES |
| 'root'@'%' | def | CREATE TABLESPACE | YES |
+--------------------+---------------+-------------------------+--------------+
140 rows in set (0.02 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
</pre>
You should be able to access from remote host:
<pre class="board-terminal">
dvdk@osboxes:~$ mysql -u root -ppassword -h 192.168.0.81
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.54-0+deb8u1 (Debian)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
</pre>
