Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

Master Setup

cat << 'EOL' >/root/sqlmaster.sh
#!/bin/bash

MASTER=192.168.1.211
DATABASE=newdatabase
SLAVEUSER=root
SLAVEHOST=sql2.freesoftwareservers.com
PASSWORD=password
SERVERID=1
 

apt-get update
apt-get install -y mysql-server mysql-client
service mysql start

sed -i '/bind-address/s/^#//g' /etc/mysql/my.cnf    
sed -i -e "s/127.0.0.1/ $MASTER/g" /etc/mysql/my.cnf
sed -i '/server-id/s/^#//g' /etc/mysql/my.cnf 
sed -i -e "s/= 1/= $SERVERID/g" /etc/mysql/my.cnf
sed -i '/log_bin/s/^#//g' /etc/mysql/my.cnf 
sed -i '/binlog_do_db/s/^#//g' /etc/mysql/my.cnf 
sed -i -e "s/include_database_name/ $DATABASE/g" /etc/mysql/my.cnf 

service mysql stop
service mysql start

mysql -p"$PASSWORD" -e "GRANT REPLICATION SLAVE ON *.* TO '$SLAVEUSER'@'$SLAVEHOST' IDENTIFIED BY '$PASSWORD';FLUSH PRIVILEGES;"
mysql -p"$PASSWORD" -e "CREATE DATABASE $DATABASE;FLUSH TABLES WITH READ LOCK;"
POS=`mysql -p"$PASSWORD" -A --skip-column-names -e"SHOW MASTER STATUS;" | awk '{print $2}'`
mysqldump -p"$PASSWORD" --opt "$DATABASE" > "$DATABASE".sql
mysql -p"$PASSWORD" -e "UNLOCK TABLES;"
echo
echo "DONT FORGET TO CHANGE POS IN SECOND SCRIPT!!!!!!"
echo "You need to set $POS in second script, so write it down!"
echo "DONT FORGET TO CHANGE POS IN SECOND SCRIPT!!!!!!"
echo
scp -oStrictHostKeyChecking=no "$DATABASE".sql "$SLAVEUSER"@"$SLAVEHOST":/tmp/
EOL
chmod +x /root/sqlmaster.sh
/root/sqlmaster.sh

Slave Setup

cat << 'EOL' >/root/sqlslave.sh
#!/bin/bash

MASTER=192.168.1.211
DATABASE=newdatabase
SLAVEUSER=root
SLAVEHOST=sql2.freesoftwareservers.com
PASSWORD=password
SERVERID=2
##CHANGE POS!!!
POS=450

apt-get update
apt-get install -y mysql-server mysql-client
service mysql start

mysql -p"$PASSWORD" -e "CREATE DATABASE "$DATABASE";"
mysql -p"$PASSWORD" "$DATABASE" < /tmp/"$DATABASE".sql

sed -i -e "s/= 1/= $SERVERID/g" /etc/mysql/my.cnf
sed -i '/server-id/s/^#//g' /etc/mysql/my.cnf 
sed -i '/log_bin/s/^#//g' /etc/mysql/my.cnf 
sed -i '/binlog_do_db/s/^#//g' /etc/mysql/my.cnf 
sed -i -e "s/include_database_name/ $DATABASE/g" /etc/mysql/my.cnf
sed -i '/log_bin/a relay-log               = /var/log/mysql/mysql-relay-bin.log' /etc/mysql/my.cnf

service mysql stop
service mysql start

mysql -p"$PASSWORD" -e "CHANGE MASTER TO MASTER_HOST='$MASTER',MASTER_USER='$SLAVEUSER', MASTER_PASSWORD='$PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  $POS;START SLAVE;"
watch "mysql -p$PASSWORD -e 'SHOW SLAVE STATUS\G'"
EOL
chmod +x /root/sqlslave.sh
/root/sqlslave.sh

Fake Data Test

Master

cat << 'EOL'>/root/faketable.sh
#!/bin/bash

DATABASE=newdatabase
PASSWORD=password

mysql -p"$PASSWORD" -e "USE $DATABASE
CREATE TABLE employeees;"
EOL
chmod +x /root/faketable.sh
/root/faketable.sh

Slave

cat << 'EOL'>/root/faketable.sh
#!/bin/bash

DATABASE=newdatabase
PASSWORD=password

mysql -p"$PASSWORD" "$DATABASE" -e "SHOW TABLES;"
EOL
chmod +x /root/faketable.sh
/root/faketable.sh

Remove Table

cat << 'EOL'>/root/faketable.sh
#!/bin/bash

DATABASE=newdatabase
PASSWORD=password

mysql -p"$PASSWORD" -e "USE $DATABASE
DROP TABLE employeees;"

EOL
chmod +x /root/faketable.sh
/root/faketable.sh

 

 

 

  • No labels