1 - Write down which is the setup you are going to do, which server is master and which server/s will be slave.
2 - Select your username/password for replications accounts. You can have one per server if you want, or one for all the mysql network.
3 - mysql> GRANT REPLICATION SLAVE ON *.*
TO 'USERNAME'@'IPFROMTHESLAVE' IDENTIFIED BY 'PASSWORD';
Username: mysql username
IPfromtheslave: ip from the mysql server that will be the one replicating the master db.
PASSWORD: the password for the replicator account.
Just a few side notes.
a) None of the passwords need to be root passwords.
b) Is not recomend to use only 1 user for replication in all the network.
4) In the master server you need to Flush all the tables, this will prevent clients from writing the db so it will keep without change while we copy over.
mysql> FLUSH TABLES WITH READ LOCK;
5) Make sure that the [mysqld] section of the my.cnf file on the master host includes a log-bin option. The section should also have a server-id=master_id option, where master_id must be a positive integer value from 1 to 232 – 1. For example:
[mysqld]
log-bin=mysql-bin
server-id=1
6) Login using another ssh client to the master server and lets create a snapshot.
mkdir /home/slave_db
rsync -vrplogDtH /var/lib/mysql /home/slave_db
You may not want to replicate the mysql database if the slave server has a different set of user accounts from those that exist on the master. In this case, you should exclude it from the archive. When the rsync is finish, just login inside mysql and type:
SHOW MASTER STATUS;
Save this info in a txt file inside the slave_db folder that we will use them laster. After you finish doing this, you can reenable the activity on the master: UNLOCK TABLES;
7) Stop the server that is to be used as a slave server and add the following to its my.cnf file:
[mysqld]
server-id=slave_id
The slave_id value, like the master_id value, must be a positive integer value from 1 to 232 – 1. In addition, it is very important that the ID of the slave be different from the ID of the master. For example:
[mysqld]
server-id=2
Remember that server-id must be unique in all the mysql network.
8) Copy the files over from the slave_db folder to the remote location. You can do this doing the following command:
rsync -e ssh -avz /home/slave_db/ root@REMOTESERVER:/var/lib/mysql
Check that all the permitions and correctly in the /var/lib/mysql folder.Remember files must be own by mysql:mysql
9) Start Mysql and enter to it, write the following changing the values that are needed:
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
10) type: START SLAVE;
##########
In a mysql shell (as root@unix and root@mysql):
mysql> flush tables with read lock;
mysql> flush logs;
mysql> system lvcreate --snapshot –-size=500M --name=backup /dev/vg01/mysql;
mysql> unlock tables;
Then back in shell land (as root@unix):
$ mount -o ro /dev/vg01/backup /mnt/tmp
$ cd /mnt/tmp/
$ tar czf backup-`date +%Y%m%d`.tgz mysql
$ umount /mnt/tmp
$ lvremove -f /dev/vg01/backup
#######
root@u-1:/home/u-1# mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.47
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> SET PASSWORD FOR root@localhost=PASSWORD('rubberchicken');
Query OK, 0 rows affected (0.06 sec)
mysql>
In this example, we set the root password to rubberchicken. Note from the above that the password wasn't set. If we try this again, we need to use the -p option to enter the password:
root@u-1:/home/u-1# mysql -u root mysql
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
root@u-1:/home/u-1# mysql -u root -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3 to server version: 3.23.47
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>
To reset a root password that you forgot (using paths on our system):
[root@host root]#killall mysqld
[root@host root]#/usr/libexec/mysqld -Sg --user=root &
You may have better luck with:
mysqld --skip-grant-tables --user=root
Go back into MySQL with the client:
[root@host root]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.41
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> UPDATE user
-> SET password=password("newpassword")
-> WHERE user="root";
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> exit;
[root@host root]#killall mysqld
Start MySQL the normal way for your system, and all is good. For Red Hat this is:
/etc/init.d/mysqld start
##
/usr/local/bin/mysqladmin -u root password 'new-password'
