Copying databases with mysql

To copy the database use mysqldump:

mysqldump -u root <DATABASE_NAME> <DUMP_FILE>.sql;

To create the new database:

mysql
mysql> create database <NEW_DATABASE_NAME>;
mysql> use <NEW_DATABASE_NAME>;
mysql> source <DUMP_FILE>.sql;


Out of resources for mysqldump - Server Fault

Recently got the error "mysqldump: Got error: 23: Out of resources when opening file './ref_ca/dsi_scenario_longs.MYD' (Errcode: 24) when using LOCK TABLES" when doing a mysqldump on Mac OSX.

Easiest option for me was to use --single-transaction as mentioned at Out of resources for mysqldump - Server Fault.

Changing user passwords in mysql

Again this is taken directly from mysql.com

You need to login into mysql and run the following sql:

UPDATE mysql.user SET Password=PASSWORD("<NEW_PASSWORD>") where user="<USER_NAME>"

You have to connect to do this and easiest is to connect as the root user:

mysql --user=root

Starting MySQL on Mac OSX

The documentation for this can be found mysql.com

The command to start mysql if the startup item is installed is:

sudo /Library/StartupItems/MySQLCOM/MySQLCOM start

Otherwise use:

/usr/local/mysql/bin/mysqld_safe and then bg to background the process.

Import MySQL text dump

Importing a mysql text dump is pretty staright forward:

mysql table_name < mysql_dump_file.sql

In addition the following options may be of use:

• --user=YOUR_USER_NAME, login as the specified user, if not specified it will user the default
• -p, prompt for password, if not specified it will use the default
• -h YOUR_HOST_NAME, the name of the host the database is on, this is often localhost which is usually the default

Put together this is used in the following way:

mysql --user=YOUR_USER_NAME -p -h localhost table_name < mysql_dump_file.sql

Backing up MySQL databases

This is a simple script to back up a MySQL database by creating a text dump of all the tables.  It is based on a script I found on the The PHP Cult.

#!/bin/bash

user=USERNAME;
password=PASSWORD;
tmp=BACKUP_DIRECTORY;
dblist=$tmp/dblist.txt

mkdir $tmp

/usr/bin/mysql -e "show databases" -u $user --password=$password | sed s/^Database//g | sed s/\|//g | sed s/\ //g  > $dblist 

for i in `cat $dblist`
do
  export FILENAME1="$i.sql"
  echo "Backing up $i to $FILENAME1 ...."
  /usr/bin/mysqldump -u $user --opt --password=$password $i > $tmp/$FILENAME1
done