MySQL Backup and restore, mysqldump
mkdir /mysql
chown mysql /mysql
mkdir /backup
chmod 764 /backup
copy existing mysql config to new file
cp /etc/my.cnf .etc/my2.cnf
#edit my2.cnf
gedit /etc/my.cnf
#configuration file
[mysqld]
datadir=/mysql/mysql
port=3306
socket=/mysql/mysql.sock
user=mysql
log-bin=/mysql/mysql1-bin
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid
#initialize second instance
mysqld --defaults-file=/etc/my2.cnf --initialize
#copy tampary password
cat /mysql/mysqld.log
#start mysql instance
mysqld --defaults-file &
#Change temporary password
mysqladmin -S/mysql.mysq.sock -u root -p password
#Provide temporary password and change the password
#Connect to second instance
mysql -u root -p<rootpassword> -S/mysql/mysql.sock
#change prompt in first instance
prompt \1>
#Change prompt in second instance
prompt \2>
#create databases in first instance
create database db1;
create database db2;
use db1
create table test (id int);
inser into test values(1);
use db2
create table testtable(id int);
insert into testtable values(1);
#For single database dump
mysqldump -u root -p<rootpassword> db1 > /backup/db1.sql
#For multiple database dump
mysqldump -u root -p<rootpassword> --databases db1 db2 > /backup/multidbs.sql
#For all databases
mysqldump -u root -p<rootpassword> --all-databases > /backup/alldbs.sql
# To restore database from single db dump
#create empty database db1 in second instance
create database db1;
#restore db1 from the dump of db1
mysql -u root -p<rootpassword> -S/mysql/mysql.sock db1</backup/db1.sql
#restore db1 and db2 from dump of db1 and db2
mysql -u root -p<rootpassword> -S/mysql/mysql.sock </backup/multidbs.sql
#restore all dbs from all dbs dump
mysql -u root -p<rootpassword> -S/mysql/mysql.sock </backup/alldbs.sql
#restore single database from all dbs dump
#drop db1 from second instance
drop database db1;
create database db1;
MySQL Backup and restore, mysqldump
mkdir /mysql
chown mysql /mysql
mkdir /backup
chmod 764 /backup
copy existing mysql config to new file
cp /etc/my.cnf .etc/my2.cnf
#edit my2.cnf
gedit /etc/my.cnf
#configuration file
[mysqld]
datadir=/mysql/mysql
port=3306
socket=/mysql/mysql.sock
user=mysql
log-bin=/mysql/mysql1-bin
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid
#initialize second instance
mysqld --defaults-file=/etc/my2.cnf --initialize
#copy tampary password
cat /mysql/mysqld.log
#start mysql instance
mysqld --defaults-file &
#Change temporary password
mysqladmin -S/mysql.mysq.sock -u root -p password
#Provide temporary password and change the password
#Connect to second instance
mysql -u root -p<rootpassword> -S/mysql/mysql.sock
#change prompt in first instance
prompt \1>
#Change prompt in second instance
prompt \2>
#create databases in first instance
create database db1;
create database db2;
use db1
create table test (id int);
insert into test values(1);
use db2
create table testtable(id int);
insert into testtable values(1);
#For single database dump
mysqldump -u root -p<rootpassword> db1 > /backup/db1.sql
#For multiple database dump
mysqldump -u root -p<rootpassword> --databases db1 db2 > /backup/multidbs.sql
#For all databases
mysqldump -u root -p<rootpassword> --all-databases > /backup/alldbs.sql
# To restore database from single db dump
#create empty database db1 in second instance
create database db1;
#restore db1 from the dump of db1
mysql -u root -p<rootpassword> -S/mysql/mysql.sock db1</backup/db1.sql
#restore db1 and db2 from dump of db1 and db2
mysql -u root -p<rootpassword> -S/mysql/mysql.sock </backup/multidbs.sql
#restore all dbs from all dbs dump
mysql -u root -p<rootpassword> -S/mysql/mysql.sock </backup/alldbs.sqlMySQL Backup and restore, mysqldump
mkdir /mysql
chown mysql /mysql
mkdir /backup
chmod 764 /backup
copy existing mysql config to new file
cp /etc/my.cnf .etc/my2.cnf
#edit my2.cnf
gedit /etc/my.cnf
#configuration file
[mysqld]
datadir=/mysql/mysql
port=3306
socket=/mysql/mysql.sock
user=mysql
log-bin=/mysql/mysql1-bin
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid
#initialize second instance
mysqld --defaults-file=/etc/my2.cnf --initialize
#copy tampary password
cat /mysql/mysqld.log
#start mysql instance
mysqld --defaults-file &
#Change temporary password
mysqladmin -S/mysql.mysq.sock -u root -p password
#Provide temporary password and change the password
#Connect to second instance
mysql -u root -p<rootpassword> -S/mysql/mysql.sock
#change prompt in first instance
prompt \1>
#Change prompt in second instance
prompt \2>
#create databases in first instance
create database db1;
create database db2;
use db1
create table test (id int);
inser into test values(1);
use db2
create table testtable(id int);
insert into testtable values(1);
#For single database dump
mysqldump -u root -p<rootpassword> db1 > /backup/db1.sql
#For multiple database dump
mysqldump -u root -p<rootpassword> --databases db1 db2 > /backup/multidbs.sql
#For all databases
mysqldump -u root -p<rootpassword> --all-databases > /backup/alldbs.sql
# To restore database from single db dump
#create empty database db1 in second instance
create database db1;
#restore db1 from the dump of db1
mysql -u root -p<rootpassword> -S/mysql/mysql.sock db1</backup/db1.sql
#restore db1 and db2 from dump of db1 and db2
mysql -u root -p<rootpassword> -S/mysql/mysql.sock </backup/multidbs.sql
#restore all dbs from all dbs dump
mysql -u root -p<rootpassword> -S/mysql/mysql.sock </backup/alldbs.sql
#restore single dbs from all dbs dump
drop database db1;
create database db1;
mysql -u root -p<rootpassword> -S/mysql/mysql.sock db1 </backup/alldbs.sql
# complete dump including routines, triggers and events
mysqldump -u root -p<rootpassword> --routines --events --triggers db1 </backup/db1_full.sql