Differences

This shows you the differences between two versions of the page.

--- vms:webdev:mysql [2014/11/03 01:45]
admin [Let Your MySQL Server Be Accessed From the Outside]
+++ vms:webdev:mysql [2014/11/03 18:09] (current)
admin [Restart MySQL]
@@ Line 1: / Line 1: @@
 ====== MySQL Server: Installation & Management ======
-The last fundamental piece of our web development environment is, of course, a database management system of choice: MySQL.
+The next fundamental piece of our web development environment is, of course, a database management system of choice: MySQL.
 ===== Install MySQL Server =====
@@ Line 37: / Line 37: @@
 ===== Add the Apache & PHP Modules =====
-NEEDS TO BE ADAPTED
 <code>
-sudo apt-get install libapache2-mod-auth-mysql php5-mysql
+> sudo apt-get install libapache2-mod-auth-mysql php5-mysql
-sudo nano /etc/php5/apache2/php.ini
 </code>
-Uncomment this line:\\
-;extension=mysql.so
-Like this:\\
-extension=mysql.so
 Now just restart Apache and you are all set!
@@ Line 59: / Line 51: @@
 Make sure to change yourpassword to a password of your choice.
-===== Install (the latest) MySQL Workbench =====
+-----
-You might be surprised here as the usual procedure for AMP installations usually would now instruct you how to install PhpMyAdmin, which is a fine MySQL management tool.
+===== Have your MySQL Databases Stored on your Host Filesystem =====
+-----
+<color darkslateblue>**This is for a VirtualBox system only**</color>\\
+As was done for our Virtual Hosts files, we'll configure our VirtualBox VM so that all our database files will be stored inside a directory that does not reside inside our VM's but rather on our host's filesystem.
-Although my personal choice goes for MySQL Workbench. It has a lot more power and features than PhpMyAdmin and can even connect to remote hosts, providing a central management tool for your development environment as well as for your production one. That's only one of the many reasons that make me think it's much better suited on a development system that PhpMyAdmin.
+Please refer to [[vms:webdev:apache#create_the_shared_www_folders|the apache virtual host setup section]] for deeper details on this configuration, as it relies on the same principles.
-You could easily install MySQL Workbench from the "Software Manager" application. Unfortunately the version that is available in there is quite outdated, 5.2.40, while version 6.0.8 is available on the [[http://dev.mysql.com/downloads/tools/workbench/|MySQL website]].
+Let's create this directory **on the host's side** (feel free to adapt this folder location to wherever you see fit):
+<code>
+> mkdir /home/<username>/Documents/webdev/mysql
+</code>
+Then, configure it as a VirtualBox shared folder:
+<code>
+> VBoxManage sharedfolder add "lamp-xd" --name "mysql-share" --hostpath "/home/<username>/Documents/webdev/mysql"
+</code>
+==== Prepare MySQL Storage Location ====
+By default all MySQL related databases files are stored under **/var/lib/mysql**. What we'll do is first stop the mysql service, then create a copy of the existing data, mount our "host" shared folder with the correct access rights, replace the copied data inside the newly mounted directory and restart the mysql service.
+Inside the VM:
+<code>
+> service mysql stop
+> sudo cp -a /var/lib/mysql /var/lib/mysql.bak
+> id mysql
+uid=104(mysql) gid=108(mysql) groups=108(mysql)
+> sudo nano /etc/fstab
+ADD:
+# automount mysqlshare as mysql user
+mysql-share	/var/lib/mysql/	vboxsf	defaults,uid=104,gid=108	0	0
+> sudo mount /var/lib/mysql
+> cp -a /var/lib/mysql.bak/* /var/lib/mysql/
+</code>
+==== Restart MySQL ====
+All should now be in place to have MySQL use our shared folder as physical database storage location.
+<code>
+> sudo service mysql start
+</code>
+=== Delete backup files ===
+Now, if all is running well, we can get rid of the backup files we initially created. In case of necessity, those files are, in fact, still present on the virtual machine's hard disk, unmounting the shared folder would reveal them.
+<code>
+> sudo rm -r /var/lib/mysql.bak
+</code>
-=== Download the .deb Installer ===
+-----
-At the bottom of the [[http://dev.mysql.com/downloads/tools/workbench/|MySQL Worbench download page]], select "Unbuntu Linux" as the platform. Then download the **Ubuntu Linux 13.04 (x86, 64-bit), DEB** version (you might have to select the 32-bit version depending on your system's architecture). Also if you didn't apply the LMDE UP7 upgrade, you'd better use the 12.04 version.
+===== Install MySQL Workbench =====
+-----
-The insatller .deb file is now in your ~/Downloads directory, let's double click on it.\\
+Even though your database files will, from now on, be stored on your host filesystem, you might find it easier to use an integrated tool to manage the different aspects of your MySQL configuration (i.e. mainly users and tables management). This could be done using [[http://www.phpmyadmin.net|PhpMyAdmin]], that could be deployed inside a dedicated virtual host.
-The "Packager Installation" app shows you the state of the process... Done !
-You now have a "MySQL Worbench" icon in your "Applications > Programming" menu.
+Although, my personal advise is to use the more versatile [[http://dev.mysql.com/downloads/tools/workbench/|MySQL Workbench]] application. The primary reason being that this one will allow you to manage not only your local development databases, but also your remote production servers, all from the same interface. Using ssh tunneling, you won't even have to allow external MySQL connections on your production machines.