MySQL Replication

These are the steps to set up Master/Slave replication on MySQL

mysql> grant replication slave, replication client on *.* to
repl@'10.%' identified by 'rvisitor';

mysql> show master status;

Change on my.cnf on salve
table_cache=750
query_cache_size = 32M
query_cache_limit = 512K
#replication settings
log_bin = replicate/mysql-bin
server_id = 20
relay_log = mysql-relay-bin
log_slave_updates = 1
log-slow-queries = mysql-slow-queries
long_query_time = 1
log-queries-not-using-indexes

replicate-do-db=test

replicate-do-db=test2

binlog_cache_size=128K


Change on my.cnf on Master
table_cache=750
query_cache_size = 32M
query_cache_limit = 512K
#replication settings
log_bin = replication/mysql-bin
binlog-do-db=test
binlog-do-db=test2
binlog-do-db=test2
expire_logs_days=10
server_id = 10
log-slow-queries = mysql-slow-queries
long_query_time = 1

Then restart mysql on both

mysql>
change master to master_host='witwicky',
master_user='repl',
master_password='rvisitor',
master_log_file='mysql-bin.000001',
master_log_pos=0;

mysql> show slave status;
mysql> start slave;
mysql> show slave status;

SHOW MASTER STATUS;

SHOW SLAVE STATUS;
SHOW MASTER LOGS;
PURGE MASTER LOGS TO 'bin-log.XYZ';

To stop execution of the binary log from the master, use STOP SLAVE:

mysql> STOP SLAVE;

When execution is stopped, the slave does not read the binary log from the master (the IO_THREAD) and stops processing events from the relay log that have not yet been executed (the SQL_THREAD). You can pause either the IO or SQL threads individually by specifying the thread type. For example:

mysql> STOP SLAVE IO_THREAD;

mysql> LOAD DATA FROM MASTER;

Simple Replication example

MySql Class - Instalation & Replication (last day)

Create 2 databases, my1 and my2, my1 will be the master and will replicate to my2.

Install my5.1 into c:\my51
In windoes, set the env variable for the new MySql instance.

Create my1:

mkdir c:/data1
mkdir c:/log1
create c:/my1.cnt

In notepad add to c:/my1.cnt:
   [mysql]
   datadir=c:/data1
   basedir=c:/my51
   port=5011
   log-bin=c:/log1/inst1
   #unique id for each instance in replication topology
   server-id=1

navigate to the my51/bin dir and type: mysqld --defaults-file=c:\my1.cnf --skip-grant-tables
Connect to the instanse (another commandline window): mysql -P 5011 (notice the upper case P)
Create the MySql db: create database mysql;
Navigate to the MySql db: use mysql;
Run sys scripts:
   source c:\my51\share\mysql_system_tables.sql
   source c:\my51\share\mysql_system_tables_data.sql
   source c:\my51\share\fill_help_tables.sql (if help files/data/info is needed)

To shutdown the instanse, exit out of the instanse into the OS: mysqladmin shutdown -P 5011
navigate to the my51/bin dir and type: mysqld --defaults-file=c:\my1.cnf
Connect to the instanse (another commandline window): mysql -P 5011 -u root (notice the upper case P and -u for the root user)

Create a replication user on the master:
   create user repl@localhost identified by 'repl';
   grant replication slave on *.* to repl@localhost;

Create my2:

mkdir c:/data2
mkdir c:/log2
create c:/my2.cnt

In notepad add to c:/my2.cnt:
   [mysql]
   datadir=c:/data2
   basedir=c:/my51
   port=5022
   log-bin=c:/log2/inst2 (set if you want to replicate my3 from my2 vs replicating from master my1)
   log_slave_update=on (set if you want to replicate my3 from my2 vs replicating from master my1)
   #unique id for each instance in replication topology
   server-id=2

navigate to the my51/bin dir and type: mysqld --defaults-file=c:\my2.cnf --skip-grant-tables
Connect to the instanse (another commandline window): mysql -P 5022 (notice the upper case P)
Create the MySql db: create database mysql;
Navigate to the MySql db: use mysql;
Run sys scripts:
   source c:\my51\share\mysql_system_tables.sql
   source c:\my51\share\mysql_system_tables_data.sql
   source c:\my51\share\fill_help_tables.sql (if help files/data/info is needed)

To shutdown the instanse, exit out of the instanse into the OS: mysqladmin shutdown -P 5022
navigate to the my51/bin dir and type: mysqld --defaults-file=c:\my2.cnf
Connect to the instanse (another commandline window): mysql -P 5022 -u root (notice the upper case P and -u for the root user)

Prep the slave for the master:
run: show master status on the master to see what logile to use and at what possition on the log file to start replicate.
change master to master_host = 'localhost', master_user='repl', master_password='repl', master_port=5011, master_log_file='inst1.000002', master_log_pos=329;
start slave;
show slave status\G

MySql Class Instructur Notes (MySqlClassInstructorNotes.txt)