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