Ubuntu: Steps to install/update MySQL to the latest DMR

No comments
For learning, testing and keep up with things, one might want to install/update the MySQL version to the latest DMR (Development Milestone Release). At the time of this writing the latest DMR for MySQL is  MySQL Community Server 5.7.8-rc.
Having chosen Ubuntu server as my operating system and since this is a development version hence not yet part of an official Ubuntu release, you have to install it directly from Oracle. You can achieve this by using the MySQL APT Repository.
Before carrying on, keep in mind that:
  • This shouldn't be done on a production environment;
  • If you're aiming for an upgrade instead of a fresh install, always backup your databases first.
Having said that, you can run the following command to download the debian package for MySQL APT repository:

shell> wget http://dev.mysql.com/get/mysql-apt-config_0.3.7-1ubuntu14.04_all.deb
To install the package, use dpkg and when it's done update the repository package lists:
shell> sudo dpkg -i mysql-apt-config_0.3.7-1ubuntu14.04_all.deb
shell> sudo apt-get update
During the installation of the package, you will be asked to choose the versions of the MySQL server and other components.
Finally, install MySQL, which will fetch the version selected on the APT repository configuration:
shell> sudo apt-get install mysql-server
In the end, log into your newly installed server. You should be greeted with the latest DMR version:
shell> stuntman@MuSQL-Lab:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.8-rc MySQL Community Server (GPL)
Remember to never do this on a real environment as it's a development version. Also, if you have an official release version installed previously, I'd recommend removing it completely before installing a development release.

No comments :

Post a Comment

MySQL - basic network security

No comments
Having secured our MySQL server and created a personal account to allow for remote administration, we can take one step further on blocking unwanted access to our database server. For this example, we'll continue to use the Ubuntu 14.04.2 LTS Server installed and configured on the previous posts.
After booting up the server, running a network check for connections, lists our server listening on ports 22 and 3306 (mysql):
We also used the Uncomplicated Firewall,  a frontend for iptables, to check if the server's firewall is enabled. It's inactive and consequently all listening ports are available to the network.
Let's adopt the following policy:
  • SSH port is open to the network and available for sysadmin staff. Access control will be implemented on dedicated network firewalls;
  • MySQL port is closed and has to be authorized on demand, on a client machine adress basis.
This will allow the database administration team to know at all times who has access and from which hosts on the network. Furthermore, it prevents remote detection of the database server with tools like NMap.
First, lets add the inbound rule for SSH connection so we don't get locked out of the server:
Then lets enable the firewall and check it's status:
The test will be to connect MySQL Workbench from a remote server and wait for failure:

Afterwards, add a specific rule for the remote machine using the ufw utility and try again:

sudo ufw allow from 192.168.204.XXX to port 3306

You should succeed and while you have the database session open, run netstat again on the server and validate your remote connection:

stuntman@MuSQL-Lab:~$ netstat -at
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 *:ssh                   *:*                     LISTEN
tcp        0      0   *:*                     LISTEN
tcp        0      0   192.168.204.X:54617     ESTABLISHED
tcp        0      0   192.168.204.X:54618     ESTABLISHED
tcp        0     64     192.168.204.X:54483     ESTABLISHED
tcp6       0      0 [::]:ssh                [::]:*                  LISTEN

Final check to see the firewall rules and get what hosts are authorized for remote access:
stuntman@MuSQL-Lab:~$ sudo ufw status
Status: active

To                         Action      From
--                         ------      ----
22/tcp                     ALLOW       Anywhere 3306       ALLOW       192.168.204.X
22/tcp (v6)                ALLOW       Anywhere (v6)
On this blog, we've covered instance lockdown, user accounts lockdown and network lockdown. All these security concerns are of added value when facing production environments with sensitive data.

No comments :

Post a Comment

MySQL sample databases: for testing and training

1 comment
Sometimes it's useful to have a set of data prepared to be used on a fresh MySQL install for testing purposes. Or you might be preparing some training or workshop and want to prepare examples with fake information. For MySQL there are some choices available:

  • Employees Sample Database: provides a combination of a large base of data (approximately 160MB) spread over six separate tables and consisting of 4 million records in total.
  • Sakila Sample Database: is intended to provide a standard schema that can be used for examples in books, tutorials, articles, samples, and so forth. Sakila sample database also serves to highlight the latest features of MySQL such as Views, Stored Procedures, and Triggers.
  • BIRT Sample Database: The schema is for Classic Models, a retailer of scale models of classic cars. The database contains typical business data such as customers, orders, order line items, products and so on.
In general, you can download a single ZIP file with the schema creation and the data loading in separate scripts.

1 comment :

Post a Comment

Print tab separated values as table using MySQL

No comments
Using mysql command line utility to get recordsets, the data rows alignment and line breaks are often  a mess. You can use the command line tool on a mysql database server to get a set of rows into an output TSV file like so:
shell>  mysql -u your_user -p < your_statement.sql > data.csv
I came up with the following python script to grab the output file and pretty print:
You can put your own filename instead of 'data.csv'. And of course, this script also works for other TSV files that don't come from mysql. To run the script you should install the python tabulate package:

No comments :

Post a Comment