MySQL Backup / mysqldump

-- 備份某個資料庫

# mysqldump -u root -p db > db.sql;

-- 備份資料庫中的某個資料表

# mysqldump -u root -p db table > backup.sql;

-- 備份所有資料庫

# mysqldump -u root -p --all-databases > backup.sql;

-- 復原一個資料庫 (需先建好db_name 這個資料庫, 若沒建立請先執行 mysqladmin create db_name 建立即可)

# mysql -u root -p db < backup.sql

-- 復原多個資料庫 ( 因為backup.sql 內已有 CREATE DATABASE指令,因此不需先建DB)

# mysql -u root -p < backup.sql

注意:
因為新版mysqldump預設會使用UTF8,所以還原較沒問題, 若為舊版的mysqldump, 則需要使用--default-character-set 指定字集

# mysql -u root -p --default-character-set=latin1 db_name < backup.sql
只輸出MySQL DB Schema

mysqldump 資料庫名稱 --no-data > 輸出檔名

Centos 5.5 install MySQL

Centos 5.5 install MySQL

 yum -y install mysql-server

vi /etc/my.cnf
--

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-character-set = utf8

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
default-character-set = utf8

--

chkconfig mysqld on

chkconfig --list mysqld

2-5 開啟

/etc/rc.d/init.d/mysqld start
--

正在初始化 MySQL 資料庫: Neither host 'ip位置' nor 'localhost' could be looked up with
/usr/bin/resolveip
Please configure the 'hostname' command to return a correct hostname.
If you want to solve this at a later stage, restart this script with
the --force option
--


 vi /etc/hosts

--
# Do not remove the following line, or various programs
# that require network functionality will fail.
#::1 localhost.localdomain localhost
127.0.0.1 localhost.localdomain localhost


--

/etc/rc.d/init.d/mysqld start

mysqladmin -u root password 'Your Password'