Move MySQL data directory from one folder to another

Moving your MySQL data from one folder to another sounds like a big job, but in reality it is not really.
Before we get started you need to bear a couple of things in mind

  • Be sure to read this entire post before you do anything.
  • Especially read the part about the ‘socket’.
  • Be sure to understand everything that is in the post
  • Some versions of unix/linux use different terminology, (for example to stop/start a service), be sure that you use the correct command.
  • Be sure you are logged in as root.
  • Remember that there will be down time!
    The length of time depends a lot on the size of the data and where you are transferring to/from.
  • Be prepared to roll back the changes if things don’t work!
    • Stop the service.
    • Copy the original my.cnf file, (see below to locate it)
    • Start the service.
  • This is all done at your own risk! If you are not careful you could really trash your data!
  • Read the errors you might get, learn about your system, normally the error should help you solve most issues.
  • Try it on your test/dev server before you do anything, (you have a test server don’t you!!)

Locate the datadir.
This is where MySQL currently has all the data saved, everything is saved in one place.

Run the script, (with phpMyAdmin or something), below and look for the entry that says “datadir”.

# locate the current data directory
SHOW VARIABLES WHERE Variable_Name LIKE “%dir”

Locate the my.cnf.
This is the file that contains the MySQL configuration, this is a very important point and you need to do it carefully.

There could be more than one file so you will need to check all of them!!!

# locate my.cnf, (the very fist line been returned from the cmd below).
# more than one could be given
mysqld –help –verbose

This will return a whole lot of information, but the very first line will contain the locations.

  • Not all the files will exist
  • Maybe _none_ of the files will exist
  • If none exist, create a file called “my.cnf” in the first folder in the list.
  • If any of the files exist, look in any of them for the entry “datadir=…”
    (of course making sure it corresponds to the datadir you located in the previous step.

Stop the MySQL service.
This is a typical case where you need to choose the right command for your repo… do your homework first…

service mysql stop
# or maybe
# /etc/init.d/mysql stop

Copy the data from the old folder to the new folder

# copy the data over, (change the directories).
# rm -rf /home/mysql/*
cp -R -p /var/lib/mysql /home/mysql

You must learn what this means, “cp -R -p” means, copy the data from the source folder to the destination folder, (“/var/lib/mysql” and “/home/mysql” in my case).

But what this also does is keep the permissions of the folder, normally a user “mysql” is created and has full permissions to that folder.

For things to keep working, you must make sure that the permissions remain.

Spend a bit of time making sure that …

  • All the files were copied
  • All the permissions have been kept.

Edit the datadir.
In the my.cnf that you edited/created earlier change, (or add), the datadir.

[mysqld]
datadir=/home/mysql
#innodb_data_home_dir
#innodb_log_group_home_dir=/home/mysql/

  • There could be other values, only edit/add the datadir=, don’t change anything else!
  • In your first step, where you located the directory, look for …
    • innodb_data_home_dir=, if the directory is the same/similar to the one you just moved, then either add an entry or edit the one that is there with the same folder.
    • innodb_log_group_home_dir=, same as above.

Start the MySQL service.

service mysql start
# or maybe
# /etc/init.d/mysql start

Immediately make sure that the server is up and running

mysqladmin status
# or using phpmyadmin# or using whatever tool you fancy…

If it is running ok, make sure that the directories are indeed valid, (same step as earlier).

# locate the current data directory
SHOW VARIABLES WHERE Variable_Name LIKE “%dir”

If the server is not started you might get a ‘socket’ error, in that case, look for where MySQL thinks it is…

SHOW VARIABLES WHERE Variable_name = “socket”

It might point to the old directory, in that case you need to edit the config one more time.

But first stop the service…

service mysql stop
# or maybe
# /etc/init.d/mysql stop

Add two socket section, (one for the client and one for mysql itself)

[client]
# …
socket=/home/mysql/mysql.sock

[mysqld]
# …
socket=/home/mysql/mysql.sock

Of course, in my example, I am using my directory and I am only showing what needs to be edited/added.

Restart the service…

service mysql start
# or maybe
# /etc/init.d/mysql start

And make sure that it is all good!

Delete the old data

If everything is working I would suggest that you back up the old data somewhere else.

To make really sure that all us good, simply rename the old folder, (don’t move it or anything, just rename), and make sure that things are working).

Install MCrypt for PHP on Windows

There are many sites around explaining how to install MCrypt for windows but none seem very straight forward.
I have PHP installed on a windows development machine and I need MCrypt installed.

The default installation might not have mcrypt or you might have missed it shomehow…

If that happens all you need to do it

  1. Go to the php download website and download the windows binary package, (not the installer).
  2. Unzip the file in a folder, (it should create a folder called something like php-5.x.yy-Win32, depending on the version number of php you got.
  3. In that folder there should be a file called “libmcrypt.dll”, copy the file to your php extension folder, (see below if you don’t know where that is).
  4. In that folder there should also be another folder called “ext”, open it and copy the file “php_mcrypt.dll” to your php extension folder.
  5. edit your php.ini file, (see below if you don’t know where that is), and add the line:

    ;;;;;;;;;;;;;;;;;;;;;;
    ; Dynamic Extensions ;
    ;;;;;;;;;;;;;;;;;;;;;;

    extension=php_mcrypt.dll

    Look for other “
    extension=…” to find where to add this line.
  6. Restart your web server, with Apache for example click on the icon on the task bar and select ‘restart’.

Where are my php extensions?

Normally they are located in your program files directory, something like “C:\Program Files\PHP\“, if you are using a non english version of Windows then the name might be different.

If it is not there then create a phpinfo.php test file and look for the path in the various variables, (look for the variables extension_dir as well as PHPRC).

Where is my php.ini file?

Same as above, normally they are located in your program files directory, something like “C:\Program Files\PHP\“, if you are using a non english version of Windows then the name might be different.

If it is not there then create a phpinfo.php test file and look for the path in the various variables, (look for the value “Loaded Configuration File).

What should my phpinfo.php file look like?

Open a blank file with your favourite text editor and type.

<?php
phpinfo();
?>

Save the file as phpinfo.php in your web folder and it should show you all the information you need about your php installation.

Install, Apache, php and MySQL on Windows


The other day I was looking for a tutorial to install Apache, php and MySQL on Windows and I wasn’t really able to find anything that cover most basic issues.
I don’t want to replace my current webserver, (IIS), I want to install Apache next to

Basic notes before we start.

  1. You must be the Administrator on the machine.
  2. This is not a how-to install a website on a server, this is how to install a website on your own development machine, (so some server security settings will be ignored).
  3. It is best to fist follow all the steps as I have listed them and then make changes that fit your needs.
  4. Each step has a ‘test’ to make sure it works, if it did not then don’t go any further, it will only make things worse. Installing PHP will not magically make Apache work.
  5. On an English version of Windows all programs are installed in the c:\program files\ directory, but it it called something else in other languages, (the variable is %programfiles%).
    If you are using a 64 bit machine then the folder is different for 86 bit applications.
    By default the installed will direct you to the program files folder, just pay attention to where that folder is.
  6. I used port number 8001, you can choose another number if you want, (but not 80 or 8080 just yet).
  7. If you have some weird and wonderful firewall you might want to turn it off during the installation, turn it back one once you know it is working. If it does not work with the firewall then you know what needs to be fixed.
  8. Turn off your Anti-virus as well if you don’t think it is clever enough to recognize Apache, PHP or MySQL.
  9. Make a note of all the folders you install things in. Don’t just blindly press ‘next’, ‘next’, …

1) Install Apache

Go to the Apache website and download the latest version, don’t get the ‘Alpha’ or ‘Beta’ version but rather get the one labelled, “the best available version’. I got version  2.2.17, (without crypto).

  1. Run the install and accept all the basic settings.
  2. in Apache folder, (c:\Program Files\Apache Software Foundation\Apache2.2\), look for the http.conf file and edit it, (it is a normal text file).
    1. Change the Listen value:

      #
      # Change this to Listen on specific IP addresses as shown below to
      # prevent Apache from glomming onto all bound IP addresses.
      #
      #Listen 12.34.56.78:80
      Listen 8001

    2. And the change the ServerName value:

      # If your host doesn’t have a registered DNS name, enter its IP address here.
      #
      #ServerName <somedomain>:80

      ServerName localhost:8001
  3. Double click on the Apache icon on the task bar, that will bring up the ‘Apache service monitor’
    The server is running, (if you don’t have IIS installed), or not running, (if there was an issue with IIS).
  4. Select the [Start] or [Restart] button, it should then start ok …
    If it does not start there is a problem, double check the settings. If you are certain that the settings are correct try another port. Instead of 9001 for example, try 8001 or something like that.
  5. Open your web browser and navigate to http://localhost:8001/ you should get a simple message “It works!!!
  6. Change the location of your website to something more developer  friendly.
    in C:\Program Files\Apache Software Foundation\Apache2.2\conf\http.conf change:

    # documents. By default, all requests are taken from this directory, but
    # symbolic links and aliases may be used to point to other locations.
    #
    DocumentRoot “c:/website”

    #
    # This should be changed to whatever you set DocumentRoot to.
    #
    <Directory “c:/website”>
  7. Don’t add a ‘/’ at the end of the path, so don’t write ‘<Directory “c:/website/“>
  8. Create a folder c:/website/, don’t put any files or anything in it.
  9. Restart the Apache server again, (on the task bar, select the apache server, “Open Apache Monitor”, select [Start] or [Restart], it should then start ok…).
  10. Open your web browser and navigate to http://localhost:8001/ you should get a message “Index of /

2) Install PHP

Go to the php.net website and download the latest stable version. Get the windows installer, I got version 5.2.14.

  1. Run the install and accept all the default settings.
  2. During the install you will be prompted to select a server, select Apache 2.2
  3. It will then ask you for the location of the Apache configuration file, select “C:\Program Files\Apache Software Foundation\Apache2.2\http.conf
  4. After the install, double check that the Apache configuration file was updated properly.
    In C:\Program Files\Apache Software Foundation\Apache2.2\http.conf at the end of the file the php installer will have added

    #BEGIN PHP INSTALLER EDITS – REMOVE ONLY ON UNINSTALL
    PHPIniDir “C:/Program Files/PHP/”
    LoadModule php5_module “C:/Program Files/PHP/php5apache2_2.dll”
    #END PHP INSTALLER EDITS – REMOVE ONLY ON UNINSTALL
  5. Edit the php.ini file to display all errors. By default the file is located in “C:/Program Files/PHP/
    Replace, “display_errors = Off” with “display_errors = On“.

    ; To output errors to STDERR with CGI/CLI:
    ;display_errors = “stderr”
    ;
    ; Default
    ;
    display_errors = On
  6. One more time, restart the Apache server, (see above).

2.1) Create a php test file.

  1. Open a blank document
  2. <?php
    echo “Hello from my website”
    ?>
  3. Save the document as “index.php” in your website root folder, (c:/website)
  4. go to http://localhost:8001/index.php
  5. You should see “Hello from my website
    1. If you see the code () then it means you did not restart the Apache service
    2. if you see nothing there might be a type in your code.
    3. Make sure that you have nothing before ”<?php’ and after ‘?>’

2.2 ) Make index.php the default

  1. In Apache configuration file, (“C:\Program Files\Apache Software Foundation\Apache2.2\conf”), Update DirectoryIndex and add add index.php

    #
    # DirectoryIndex: sets the file that Apache will serve if a directory
    # is requested.
    #
    <IfModule dir_module>
    DirectoryIndex index.html index.php
  2. The order is important, if you have both, index.html and index.php in your website then index.html will be displayed.
  3. Restart the Apache server, “Open Apache Monitor”, select [Start] or [Restart], it should then start ok…
  4. Go to http://localhost:8001/ and you should see “Hello from my website
    1. if you see “Index of /” you didn’t restart the Apache service
    2. if you don’t see “Hello from my website” or “Index of /“, do you have an index.html file that is showing?

3) Install MySQL

Go to the MySQL website and download the latest stable version. Get the windows installer, I got version 5.1.52 You can get version 32bit or 64bit. Get the recommended, ‘Essential’  download.

For version 5.2, (and earlier), you also need to download mysql extension (PHP 5.2.0).

  1. Do the typical install and accept all settings.
  2. The last step is to configure

3.1) Configure the MySQL server now.

Select the following, (mostly default), options

  1. Detailed configuration.
  2. Developer machine.
  3. Multifunctional database
  4. Select/Edit “c:\website\data\” rather than the default, (this is easier to locate and/or port to another machine).
  5. DSS/OLAP server instance configuration.
  6. port number 3306.
  7. Best support for Multilingualism, (you will need it at some stage and it does not harm even if you think you won’t need it).
  8. Service name “MySQL”.
  9. Set the root password, (and write it down!). Even for development it is good practice to use a strong password.
  10. Press the ‘Execute …’, (takes a bit of time). You should get no errors.

If you get an error on the last step it is almost because of a security issue, (a Windows/Firewall/Anti virus issue).

3.2 Tell PHP about MySQL.

In the old days PHP would install the MySQL extension by default, in 5.x that changed and you had to download it. In 5.3 it is bundled again.
Either way, make sure that PHP knows how to work with PHP, the following steps might already be done for you by the installer(s), if it is don’t overwrite the settings.

  1. Unzip the mysql extension you downloaded earlier, (http://dev.mysql.com/downloads/mysql/) and copy them to your php directory.
    By default located in c:\program files\php\
    There should be 2 files, libmysql.dll and php_mysql.dll, copy them both.
    If the files already exist, do not overwrite those files.
  2. Edit your php.ini file (By default located in c:\program files\php\php.ini)
    Add the mysql extensions.

    ;;;;;;;;;;;;;;;;;;;;;;
    ; Dynamic Extensions ;
    ;;;;;;;;;;;;;;;;;;;;;;

    extension=php_mysql.dll
  3. On the task bar, select the Apache server, “Open Apache Monitor”, select [Start] or [Restart], it should then start ok…
    1. If Apache does not restart, , did you copy both dlls or just one?
    2. Did you get the right files, (on the download site, did you get the right MySQL version and right php version)?
    3. Did you download the mysqli extension or the mysql extension, (note the extra ‘i’)?

3.3 Create a mysql/php test files.

  1. Open/create a new, empty file.
  2. Type the following
    <?php
    // Report all PHP errors (see changelog)
    error_reporting(E_ALL);

    $con = mysql_connect(‘localhost’,’root’,’<yourpassword>‘);


    if (!$con)
    {
    die(‘Could not connect: ‘ . mysql_error());
    }
    echo “Connected to database!!”;
    ?>

  3. Save the file “c:\website\connect.php
  4. Go to your site and check that you are connected to your MySQL database
    http://localhost:8001/connect.php

    1. If you get ‘Could not connect’, did you put the right user name and password, (replace <yourpassword>)?
    2. If you see nothing at all check your php.ini file to display errors, (see above).
    3. If you see “Call to undefined function mysql_connect()” check your php.ini file for mysql settings. If they look ok, restart Apache again and make sure it has no errors.

Concusion

This should be it, your web server should be up and running, you can now restart your firewall, check that the website is still running, then restart your anti virus.

Once all this is running you can play with various settings, remember that you, (almost always), need to restart Apache once you made a change to the http.conf or php.ini files.