Friday, September 28, 2018

Create a MySQL Computer Engine VM Instance Using Ubuntu in Google Cloud


1. Create Ubuntu VM Instance




2. Change Firewall Settings


3. Install MySQL 


Connected, host fingerprint: ssh-rsa 2048 13:5D:03:99:0D:A2:B8:FB:8B:2A:DB:51:37:90:C7:5B:00:33:0E:0D:D9:77:E2:BB:EC:04:49:9E:55:F7:83:86
Welcome to Ubuntu 16.04.5 LTS (GNU/Linux 4.15.0-1019-gcp x86_64)

 * Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage

  Get cloud support with Ubuntu Advantage Cloud Guest:
    http://www.ubuntu.com/business/services/cloud

0 packages can be updated.
0 updates are security updates.



The programs included with the Ubuntu system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Ubuntu comes with ABSOLUTELY NO WARRANTY, to the extent permitted by
applicable law.

[email protected]:~$ sudo apt-get update
Hit:1 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial InRelease
Get:2 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates InRelease [109 kB]                                            
Get:3 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-backports InRelease [107 kB]                                          
Get:4 http://security.ubuntu.com/ubuntu xenial-security InRelease [107 kB]                                                           
Get:5 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main Sources [868 kB]                                                     
Get:6 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/restricted Sources [4,808 B]                         
Get:7 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/universe Sources [7,728 kB]        
Get:8 http://archive.canonical.com/ubuntu xenial InRelease [11.5 kB]                                    
Get:9 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/multiverse Sources [179 kB]                  
Get:10 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/universe amd64 Packages [7,532 kB]
Get:11 http://security.ubuntu.com/ubuntu xenial-security/main Sources [134 kB]                               
Get:12 http://security.ubuntu.com/ubuntu xenial-security/restricted Sources [2,116 B]                                 
Get:13 http://security.ubuntu.com/ubuntu xenial-security/universe Sources [75.8 kB]                          
Get:14 http://security.ubuntu.com/ubuntu xenial-security/multiverse Sources [2,088 B]                                    
Get:15 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/universe Translation-en [4,354 kB]                            
Get:16 http://security.ubuntu.com/ubuntu xenial-security/main amd64 Packages [556 kB]                                        
Get:17 http://security.ubuntu.com/ubuntu xenial-security/main Translation-en [235 kB]                          
Get:18 http://security.ubuntu.com/ubuntu xenial-security/universe amd64 Packages [379 kB]                                       
Get:19 http://archive.canonical.com/ubuntu xenial/partner amd64 Packages [3,124 B]   
Get:20 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/multiverse amd64 Packages [144 kB]                             
Get:21 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/multiverse Translation-en [106 kB]                   
Get:22 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/main Sources [321 kB]                   
Get:23 http://security.ubuntu.com/ubuntu xenial-security/universe Translation-en [144 kB]            
Get:24 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/restricted Sources [2,528 B]
Get:25 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/universe Sources [222 kB]    
Get:26 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/multiverse Sources [8,388 B]
Get:27 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/main amd64 Packages [849 kB] 
Get:28 http://security.ubuntu.com/ubuntu xenial-security/multiverse amd64 Packages [3,460 B]         
Get:29 http://archive.canonical.com/ubuntu xenial/partner Translation-en [1,616 B]                   
Get:30 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/main Translation-en [347 kB]
Get:31 http://security.ubuntu.com/ubuntu xenial-security/multiverse Translation-en [1,744 B]
Get:32 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/universe amd64 Packages [691 kB]
Get:33 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/universe Translation-en [279 kB]
Get:34 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/multiverse amd64 Packages [16.4 kB]
Get:35 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/multiverse Translation-en [8,344 B]
Get:36 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-backports/main Sources [4,488 B]
Get:37 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-backports/universe Sources [6,740 B]
Get:38 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-backports/main amd64 Packages [6,756 B]
Get:39 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-backports/main Translation-en [4,180 B]
Get:40 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-backports/universe amd64 Packages [7,568 B]
Get:41 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-backports/universe Translation-en [4,048 B]
Fetched 25.6 MB in 5s (5,012 kB/s)             
Setting up tzdata (2018e-0ubuntu0.16.04) ...

Current default time zone: 'Etc/UTC'
Local time is now:      Fri Sep 28 18:36:41 UTC 2018.
Universal Time is now:  Fri Sep 28 18:36:41 UTC 2018.
Run 'dpkg-reconfigure tzdata' if you wish to change it.

Setting up libisc-export160 (1:9.10.3.dfsg.P4-8ubuntu1.11) ...
Setting up libdns-export162 (1:9.10.3.dfsg.P4-8ubuntu1.11) ...
Setting up libisc160:amd64 (1:9.10.3.dfsg.P4-8ubuntu1.11) ...
Setting up libdns162:amd64 (1:9.10.3.dfsg.P4-8ubuntu1.11) ...
Setting up libisccc140:amd64 (1:9.10.3.dfsg.P4-8ubuntu1.11) ...
Setting up libisccfg140:amd64 (1:9.10.3.dfsg.P4-8ubuntu1.11) ...
Setting up libbind9-140:amd64 (1:9.10.3.dfsg.P4-8ubuntu1.11) ...
Setting up liblwres141:amd64 (1:9.10.3.dfsg.P4-8ubuntu1.11) ...
Setting up bind9-host (1:9.10.3.dfsg.P4-8ubuntu1.11) ...
Setting up dnsutils (1:9.10.3.dfsg.P4-8ubuntu1.11) ...
Setting up libcurl3-gnutls:amd64 (7.47.0-1ubuntu2.9) ...
Setting up curl (7.47.0-1ubuntu2.9) ...
Setting up libglib2.0-data (2.48.2-0ubuntu4.1) ...
Setting up libcurl3:amd64 (7.47.0-1ubuntu2.9) ...
Processing triggers for libc-bin (2.23-0ubuntu10) ...

[email protected]:~$ sudo apt-get upgrade
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Calculating upgrade... Done
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

[email protected]:~$ sudo apt-get dist-upgrade
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Calculating upgrade... Done
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.


[email protected]:~$ sudo apt-get install mysql-server
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.0-5 libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl
  libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server-5.7 mysql-server-core-5.7
Suggested packages:
  libdata-dump-perl libipc-sharedcache-perl libwww-perl mailx tinyca
The following NEW packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.0-5 libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl
  libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.7 mysql-server-core-5.7
0 upgraded, 21 newly installed, 0 to remove and 0 not upgraded.
Need to get 19.4 MB of archives.
After this operation, 162 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/main amd64 mysql-common all 5.7.23-0ubuntu0.16.04.1 [15.4 kB]
Get:2 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libaio1 amd64 0.3.110-2 [6,356 B]
Get:3 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/main amd64 mysql-client-core-5.7 amd64 5.7.23-0ubuntu0.16.04.1 [6,675 kB]
Get:4 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/main amd64 mysql-client-5.7 amd64 5.7.23-0ubuntu0.16.04.1 [1,662 kB]
Get:5 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/main amd64 mysql-server-core-5.7 amd64 5.7.23-0ubuntu0.16.04.1 [7,765 kB]
Get:6 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libevent-core-2.0-5 amd64 2.0.21-stable-2ubuntu0.16.04.1 [70.6 kB]
Get:7 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/main amd64 mysql-server-5.7 amd64 5.7.23-0ubuntu0.16.04.1 [2,597 kB]
Get:8 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libhtml-tagset-perl all 3.20-2 [13.5 kB]
Get:9 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 liburi-perl all 1.71-1 [76.9 kB]
Get:10 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libhtml-parser-perl amd64 3.72-1 [86.1 kB]
Get:11 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libcgi-pm-perl all 4.26-1 [185 kB]
Get:12 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libfcgi-perl amd64 0.77-1build1 [32.3 kB]
Get:13 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libcgi-fast-perl all 1:2.10-1 [10.2 kB]
Get:14 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libencode-locale-perl all 1.05-1 [12.3 kB]
Get:15 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libhtml-template-perl all 2.95-2 [60.4 kB]
Get:16 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libtimedate-perl all 2.3000-2 [37.5 kB]
Get:17 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libhttp-date-perl all 6.02-1 [10.4 kB]
Get:18 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libio-html-perl all 1.001-1 [14.9 kB]
Get:19 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 liblwp-mediatypes-perl all 6.02-1 [21.7 kB]
Get:20 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial/main amd64 libhttp-message-perl all 6.11-1 [74.3 kB]
Get:21 http://us-east1.gce.archive.ubuntu.com/ubuntu xenial-updates/main amd64 mysql-server all 5.7.23-0ubuntu0.16.04.1 [10.8 kB]
Fetched 19.4 MB in 0s (29.1 MB/s)   
Preconfiguring packages ...
Selecting previously unselected package mysql-common.
(Reading database ... 71002 files and directories currently installed.)
Preparing to unpack .../mysql-common_5.7.23-0ubuntu0.16.04.1_all.deb ...
Unpacking mysql-common (5.7.23-0ubuntu0.16.04.1) ...
Selecting previously unselected package libaio1:amd64.
Preparing to unpack .../libaio1_0.3.110-2_amd64.deb ...
Unpacking libaio1:amd64 (0.3.110-2) ...
Selecting previously unselected package mysql-client-core-5.7.
Preparing to unpack .../mysql-client-core-5.7_5.7.23-0ubuntu0.16.04.1_amd64.deb ...
Unpacking mysql-client-core-5.7 (5.7.23-0ubuntu0.16.04.1) ...
Selecting previously unselected package mysql-client-5.7.
Preparing to unpack .../mysql-client-5.7_5.7.23-0ubuntu0.16.04.1_amd64.deb ...
Unpacking mysql-client-5.7 (5.7.23-0ubuntu0.16.04.1) ...
Selecting previously unselected package mysql-server-core-5.7.
Preparing to unpack .../mysql-server-core-5.7_5.7.23-0ubuntu0.16.04.1_amd64.deb ...
Unpacking mysql-server-core-5.7 (5.7.23-0ubuntu0.16.04.1) ...
Selecting previously unselected package libevent-core-2.0-5:amd64.
Preparing to unpack .../libevent-core-2.0-5_2.0.21-stable-2ubuntu0.16.04.1_amd64.deb ...
Unpacking libevent-core-2.0-5:amd64 (2.0.21-stable-2ubuntu0.16.04.1) ...
Processing triggers for libc-bin (2.23-0ubuntu10) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up mysql-common (5.7.23-0ubuntu0.16.04.1) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Selecting previously unselected package mysql-server-5.7.
(Reading database ... 71170 files and directories currently installed.)
Preparing to unpack .../mysql-server-5.7_5.7.23-0ubuntu0.16.04.1_amd64.deb ...
Unpacking mysql-server-5.7 (5.7.23-0ubuntu0.16.04.1) ...
Selecting previously unselected package libhtml-tagset-perl.
Preparing to unpack .../libhtml-tagset-perl_3.20-2_all.deb ...
Unpacking libhtml-tagset-perl (3.20-2) ...
Selecting previously unselected package liburi-perl.
Preparing to unpack .../liburi-perl_1.71-1_all.deb ...
Unpacking liburi-perl (1.71-1) ...
Selecting previously unselected package libhtml-parser-perl.
Preparing to unpack .../libhtml-parser-perl_3.72-1_amd64.deb ...
Unpacking libhtml-parser-perl (3.72-1) ...
Selecting previously unselected package libcgi-pm-perl.
Preparing to unpack .../libcgi-pm-perl_4.26-1_all.deb ...
Unpacking libcgi-pm-perl (4.26-1) ...
Selecting previously unselected package libfcgi-perl.
Preparing to unpack .../libfcgi-perl_0.77-1build1_amd64.deb ...
Unpacking libfcgi-perl (0.77-1build1) ...
Selecting previously unselected package libcgi-fast-perl.
Preparing to unpack .../libcgi-fast-perl_1%3a2.10-1_all.deb ...
Unpacking libcgi-fast-perl (1:2.10-1) ...
Selecting previously unselected package libencode-locale-perl.
Preparing to unpack .../libencode-locale-perl_1.05-1_all.deb ...
Unpacking libencode-locale-perl (1.05-1) ...
Selecting previously unselected package libhtml-template-perl.
Preparing to unpack .../libhtml-template-perl_2.95-2_all.deb ...
Unpacking libhtml-template-perl (2.95-2) ...
Selecting previously unselected package libtimedate-perl.
Preparing to unpack .../libtimedate-perl_2.3000-2_all.deb ...
Unpacking libtimedate-perl (2.3000-2) ...
Selecting previously unselected package libhttp-date-perl.
Preparing to unpack .../libhttp-date-perl_6.02-1_all.deb ...
Unpacking libhttp-date-perl (6.02-1) ...
Selecting previously unselected package libio-html-perl.
Preparing to unpack .../libio-html-perl_1.001-1_all.deb ...
Unpacking libio-html-perl (1.001-1) ...
Selecting previously unselected package liblwp-mediatypes-perl.
Preparing to unpack .../liblwp-mediatypes-perl_6.02-1_all.deb ...
Unpacking liblwp-mediatypes-perl (6.02-1) ...
Selecting previously unselected package libhttp-message-perl.
Preparing to unpack .../libhttp-message-perl_6.11-1_all.deb ...
Unpacking libhttp-message-perl (6.11-1) ...
Selecting previously unselected package mysql-server.
Preparing to unpack .../mysql-server_5.7.23-0ubuntu0.16.04.1_all.deb ...
Unpacking mysql-server (5.7.23-0ubuntu0.16.04.1) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libio-html-perl (1.001-1) ...
Setting up liblwp-mediatypes-perl (6.02-1) ...
Setting up libhttp-message-perl (6.11-1) ...
Setting up mysql-server (5.7.23-0ubuntu0.16.04.1) ...
Processing triggers for libc-bin (2.23-0ubuntu10) ...
Processing triggers for systemd (229-4ubuntu21.4) ...
Processing triggers for ureadahead (0.100.0-19) ...


[email protected]:~$ sudo mysql_install_db
2018-09-28 18:38:23 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2018-09-28 18:38:23 [ERROR]   The data directory needs to be specified.



[email protected]:~$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
Error: Access denied for user 'root'@'localhost' (using password: YES)
[email protected]:~$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

#
# The MySQL database server configuration file.
#
# The MySQL database server configuration file.
LOW    Length >= 8
#
# The MySQL database server configuration file.
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

#
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 

Invalid option provided.

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : 0

 ... skipping.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
All done! 
[email protected]:~$ 


4. Allow Remote Access to MySQL

4.1 Change MySql bind interface:

[email protected]:/etc/mysql/mysql.conf.d$ netstat -ntlp | grep 3306
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      -               
[email protected]:/etc/mysql/mysql.conf.d$ sudo vi mysqld.cnf
.....
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address          = 127.0.0.1
bind-address            = 0.0.0.0
#
.....

[email protected]:/etc/mysql/mysql.conf.d$ sudo /etc/init.d/mysql restart
sudo: unable to resolve host ip-10-1-1-50
Restarting mysql (via systemctl): mysql.service.
[email protected]:/etc/mysql/mysql.conf.d$ netstat -ntlp | grep 3306
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      -   




[email protected]:~$ 
[email protected]:~$ netstat -ntlp | grep 3306
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      -               
[email protected]:~$ cd /etc/mysql/
[email protected]:/etc/mysql$ cd mysql.conf.d/
[email protected]:/etc/mysql/mysql.conf.d$ ls
mysqld.cnf  mysqld_safe_syslog.cnf
[email protected]:/etc/mysql/mysql.conf.d$ sudo vi mysqld.cnf

[email protected]:/etc/mysql/mysql.conf.d$ sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
[email protected]:/etc/mysql/mysql.conf.d$ netstat -ntlp | grep 3306
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      -               
[email protected]:/etc/mysql/mysql.conf.d$ telnet 35.196.130.113 3306
Trying 35.196.130.113...
Connected to 35.196.130.113.
Escape character is '^]'.
G Host '35.196.130.113' is not allowed to connect to this MySQL server Connection closed by foreign host.
[email protected]:/etc/mysql/mysql.conf.d$ ping localhost
PING localhost (127.0.0.1) 56(84) bytes of data.
64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.017 ms
64 bytes from localhost (127.0.0.1): icmp_seq=2 ttl=64 time=0.035 ms
^C
--- localhost ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1006ms
rtt min/avg/max/mdev = 0.017/0.026/0.035/0.009 ms


[email protected]:/etc/mysql/mysql.conf.d$ telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
[
5.7.23-0ubuntu0.16.04.1   %y<uD|Y    Hjpg1U
 *Y% mysql_native_passwordConnection closed by foreign host.


jo[email protected]:/etc/mysql/mysql.conf.d$ sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.




4.2 Allow a new user to remote access Mysql DB

1) Connect to mysql
mysql -u root -p
2) Create user
CREATE USER 'wpuser'@'%' IDENTIFIED BY '[email protected]';
3) Grant permissions
 GRANT ALL PRIVILEGES ON *.* TO 'wpuser'@'%' WITH GRANT OPTION;
4) Flush priviledges
FLUSH PRIVILEGES;





[email protected]:/etc/mysql/mysql.conf.d$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, 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> create user 'user'@'%' identified by '[email protected]';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> quit
Bye
[email protected]:/etc/mysql/mysql.conf.d$ 

                   



4.3 Test Remote Access from another server


[email protected]:~$ ping 35.196.130.113
PING 35.196.130.113 (35.196.130.113) 56(84) bytes of data.
64 bytes from 35.196.130.113: icmp_seq=1 ttl=64 time=1.58 ms
^C
--- 35.196.130.113 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 1.586/1.586/1.586/0.000 ms
[email protected]:~$ ssh 35.196.130.113
The authenticity of host '35.196.130.113 (35.196.130.113)' can't be established.
ECDSA key fingerprint is SHA256:LKj9GFs7SUXaOUGDAf+byHaCg+CMvGDPFmvPCOtU0jY.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '35.196.130.113' (ECDSA) to the list of known hosts.
Permission denied (publickey).

[email protected]:~$ telnet 35.196.130.113 22
Trying 35.196.130.113...
Connected to 35.196.130.113.
Escape character is '^]'.
SSH-2.0-OpenSSH_7.2p2 Ubuntu-4ubuntu2.4

Protocol mismatch.
Connection closed by foreign host.
[email protected]:~$ telnet 35.196.130.113 3306
Trying 35.196.130.113...
Connected to 35.196.130.113.
Escape character is '^]'.
F Host '35.196.211.26' is not allowed to connect to this MySQL server Connection closed by foreign host.
[email protected]:~$ mysql -h 35.196.130.113 -u root
ERROR 1130 (HY000): Host '35.196.211.26' is not allowed to connect to this MySQL server  
[email protected]:~$ mysql -h 35.196.130.113 -u root
ERROR 1045 (28000): Access denied for user 'root'@'35.196.211.26' (using password: NO)
[email protected]:~$ mysql -h 35.196.130.113 -u root
ERROR 1045 (28000): Access denied for user 'root'@'35.196.211.26' (using password: NO)
[email protected]:~$ mysql -h 35.196.130.113 -u user
ERROR 1045 (28000): Access denied for user 'user'@'35.196.211.26' (using password: NO)
[email protected]:~$ mysql -h 35.196.130.113 -u user -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, 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> 
mysql> 




Basic Mysql commands:


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+
5 rows in set (0.02 sec)
mysql> use wordpress
Database changed
mysql> show tables;
Empty set (0.00 sec)

5. Remote Wordpress Config to Access MySQL

5.1 Install Apache2
sudo apt-get install apache2


5.2 Install PHP

sudo apt-get install php libapache2-mod-php php-mcrypt php-mysql


5.3 Install WP
cd ~
wget http://wordpress.org/latest.tar.gz
tar xzvf latest.tar.gz
sudo apt-get update
sudo apt-get install php5-gd libssh2-php


5.4 Configure WP
[email protected]:~$ cd ~/wordpress
[email protected]:~/wordpress$ cp wp-config-sample.php wp-config.php
[email protected]:~/wordpress$ curl -s https://api.wordpress.org/secret-key/1.1/salt/
define('AUTH_KEY',         '/c|BO/-!OU8+unm:*{r.iZD/7.Ayge],.Unawgno^!%([email protected]#U7fdW#3Q<hcq<&/e');
define('SECURE_AUTH_KEY',  '5d;:+VIC&Lh6-F>IoYk6eQag=n,;/KB9^589o|Ou)_N-_yb;Bo7$t,}?Uc 4rtgO');
define('LOGGED_IN_KEY',    '=/lVrQ?h6yt(yYi9VS=1kZehJ67z4o]bg{dO-jIXl*r8h~3qeUd9BK6!3|#@;.!2');
define('NONCE_KEY',        'HuHK`7HwsW7|jvAXY(AXWFYcWmbvW2-xwNw[HXM0nu?4P.GATiYXZhLsAiaqoF<N');
define('AUTH_SALT',        '!qV`*1GT;dRH-Zf6Ylwa*I]]|Y/seeoHqc*-:N11xi.!<d8X|<bA`4ot-2oJrmNO');
define('SECURE_AUTH_SALT', '+TgB6l[^8LgLp65);+PtT2DYideMa>UD=SJZ2jD`[email protected]>;i:X1JLU{`g');
define('LOGGED_IN_SALT',   'R`~Rhkuzz}v|OM|<$JSjkBd,~y/fsePw[q6:hH>GR{u``I/pmRa_]BkG%g(@K36x');
define('NONCE_SALT',       'C305sfDZGO5~J3XLDp|QLw2]4QYZ1B/#UM[pC6Lt+tvkBi]9pl-k^]e6Cw$,DM1(');
[email protected]:~/wordpress$ 
[email protected]:~/wordpress$ nano wp-config.php



/var/www/html/wp-config.php
. . .

define('DB_NAME', 'WP_DB');

/** MySQL database username */
define('DB_USER', 'WP_USER');

/** MySQL database password */
define('DB_PASSWORD', '[email protected]');

. . .

define('FS_METHOD', 'direct');


sudo chown -R www-data:www-data /var/www
sudo find /var/www/ -type d -exec chmod 755 {} \;
sudo find /var/www/ -type f -exec chmod 644 {} \;


[email protected]:/var/www/html$ mv index.html apache.html

Now you should be able to access homepage.

Sometimes, you may be able to access home page, but not able to get into any other links. That is because of permalinks not working.
In newer versions of apache2, you need to enable the module:
sudo a2enmod rewrite
sudo service apache2 restart
You may also need to modify the apache2.conf file.
sudo nano /etc/apache2/apache2.conf
Change your web directory override rule to AllowOverride All.
<Directory /var/www/>
    Options Indexes FollowSymLinks
    AllowOverride All
    Require all granted
</Directory>
After that, restart the service again.
sudo service apache2 restart

References:





No comments:

Post a Comment