Fix permissions after changing MySQL data directory

• sudo vi /etc/apparmor.d/usr.sbin.mysqld

  /path/to/new_mysql_data_dir/ r,
  /path/to/new_mysql_data_dir/** rwk,

• sudo chown -R mysql:mysql /path/to/new_mysql_data_dir/

• sudo /etc/init.d/apparmor restart
  sudo /etc/init.d/mysql restart

mysql: changing data directory requires change of apparmor settings

When changing mysql data directory from /var/lib/mysql to /Volumes/Store/mysql:

sudo service mysql stop
sudo mv /var/lib/mysql /Volumes/Store/mysql
sudo service mysql start

Your mysql will fail to start, and run dmesg, you can find the follow error:

[ 2012.069037] type=1400 audit(1357622820.131:47): apparmor="DENIED" operation="mknod" parent=1 profile="/usr/sbin/mysqld" name="/Volumes/Store/mysql/nsp-precise.lower-test" pid=5300 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0

This is because apparmor are preventing mysqld from accessing unregistered directories.
To solve the problem, edit /etc/apparmor.d/usr.sbin.mysqld as root, and add following lines:

/Volumes/Store/mysql/ r,
/Volumes/Store/mysql/** rwk,

then, start mysql server

sudo service mysql start

See also

• http://informationideas.com/news/2010/04/15/changing-mysql-data-directory-require-change-to-apparmor/

Ubuntu: Migrate all mysql databases from one server to another

1. On the new server, install mysql server:

   sudo apt-get install mysql-server mysql-client

2. On the new server, stop the mysql server:

   sudo service mysql stop

3. On the new server, backup /var/lib/mysql:

   sudo mv /var/lib/mysql /var/lib/mysql.bak

4. On the old server, stop the mysql server:

   sudo /etc/init.d/mysql stop

5. On the new server, mirror /var/lib/mysql directory from the old server:

   sudo rsync -avz root@old_server:/var/lib/mysql /var/lib/

6. On the new server, run

   sudo mysql_upgrade -u root -p

   (NOTE: if you forget the root user password, you can reset the root password.)
7. Start mysql server on the new server:

   sudo service mysql start

See also

• Copying mysql databases to another machine
• How can I move a database from one server to another?

Ubuntu: Install apache2, php5 and mysql

sudo apt-get install mysql-server mysql-client apache2 php5 libapache2-mod-php5 php5-mysql

mysql: reset root password

1. Stop mysql service:

   sudo service mysql stop

2. Start mysqld with following arguments: --skip-grant-tables and --skip-networking:

   sudo mysqld --skip-grant-tables --skip-networking &

3. Run following command to upgrade the database:

   sudo mysql_upgrade

4. Connect to mysql server:

   mysql

5. Run following SQL clauses to change root password:

   UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
   FLUSH PRIVILEGES;
   

6. Stop the msyql process:

   sudo killall -9 mysqld

7. Start mysql service:

   sudo service mysql stop

See also

• MYSQL documentation

MySQL: select records from multiple records

• Full join(Cross Join):
  

  SELECT employee.id, employee.name, salary.amount FROM employee, salary WHERE employee.id=salary.employee_id;
  

• Left/Right join:
  

  SELECT employee.id, employee.name, salary.amount LEFT JOIN ON employee.id=salary.employee_id;
  

SEE ALSO

• Retrieve records from multiple tables

MySQL: export data to csv file

SELECT employee.id, employee.name, employee.salary 
INTO OUTFILE '/tmp/employee.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM employee;