MySQL 5.7 Release Notes
[+/-]
This section describes how to set up a MySQL server to use binary log file position based replication. There are a number of different methods for setting up replication, and the exact method to use depends on how you are setting up replication, and whether you already have data within your master database.
There are some generic tasks that are common to all setups:
On the master, you must enable binary logging and configure a unique server ID. This might require a server restart. See Section 16.1.2.1, “Setting the Replication Master Configuration”.
On each slave that you want to connect to the master, you must configure a unique server ID. This might require a server restart. See Section 16.1.2.5.1, “Setting the Replication Slave Configuration”.
Optionally, create a separate user for your slaves to use during authentication with the master when reading the binary log for replication. See Section 16.1.2.2, “Creating a User for Replication”.
Before creating a data snapshot or starting the replication process, on the master you should record the current position in the binary log. You need this information when configuring the slave so that the slave knows where within the binary log to start executing events. See Section 16.1.2.3, “Obtaining the Replication Master Binary Log Coordinates”.
If you already have data on the master and want to use it to synchronize the slave, you need to create a data snapshot to copy the data to the slave. The storage engine you are using has an impact on how you create the snapshot. When you are using
MyISAM, you must stop processing statements on the master to obtain a read-lock, then obtain its current binary log coordinates and dump its data, before permitting the master to continue executing statements. If you do not stop the execution of statements, the data dump and the master status information will not match, resulting in inconsistent or corrupted databases on the slaves. For more information on replicating aMyISAMmaster, see Section 16.1.2.3, “Obtaining the Replication Master Binary Log Coordinates”. If you are usingInnoDB, you do not need a read-lock and a transaction that is long enough to transfer the data snapshot is sufficient. For more information, see Section 14.19, “InnoDB and MySQL Replication”.Configure the slave with settings for connecting to the master, such as the host name, login credentials, and binary log file name and position. See Section 16.1.2.5.2, “Setting the Master Configuration on the Slave”.
Note
Certain steps within the setup process require the
SUPER privilege. If you do not
have this privilege, it might not be possible to enable
replication.
After configuring the basic options, select your scenario:
To set up replication for a fresh installation of a master and slaves that contain no data, see Section 16.1.2.5.3, “Setting Up Replication between a New Master and Slaves”.
To set up replication of a new master using the data from an existing MySQL server, see Section 16.1.2.5.4, “Setting Up Replication with Existing Data”.
To add replication slaves to an existing replication environment, see Section 16.1.2.6, “Adding Slaves to a Replication Environment”.
Before administering MySQL replication servers, read this entire chapter and try all statements mentioned in Section 13.4.1, “SQL Statements for Controlling Master Servers”, and Section 13.4.2, “SQL Statements for Controlling Slave Servers”. Also familiarize yourself with the replication startup options described in Section 16.1.6, “Replication and Binary Logging Options and Variables”.
---
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
---
* Which means that Mysql password for user 'debian-sys-maint' isn't the same as in file /etc/mysql/debian.cnf.
* Because this password is randomly generated, they are different for each installation, and we have to 'synchronize' this password between debian.cnf file and Mysql privileges.
1 - On the master, get a copy of the password string in /etc/mysql/debian.cnf
2 - On the slave, stop the Mysql server, edit /etc/mysql/debian.cnf and replace the password by the one of the master. Start the slave server.
If you want to change it
1 - Launch a Mysql client and select 'mysql' database
2 - run this : UPDATE `user` SET `Password` = password('[password]') where user='debian-sys-maint'
(the password seems to be encrypted but is not)
3 - stop the Mysql server (you get an error for 'debian-sys-maint')
4 - change and put same [password] in /etc/mysql/debian.cnf file
"ERROR 1218 (08S01): Error connecting to master: Lost connection to MySQL server during query"
Make sure that ,on you're master replication server, you change the following line in your my.cnf:
bind-address 127.0.0.1
to
#bind-address 127.0.0.1
Or change the address to your FQDN or ip-addres
This works for InnoDB tables, if you have set transactionality to REPEATABLE-READ.
M: begin;
M: flush tables with read lock;
M: show master status;
M: show databases;
M: (for each db:)
M:__ show tables in $db;
M:__ (for each table:)
M:____ select 1 from $db.$table limit 1;
M: unlock tables;
M: set time_zone = '+00:00';
S: set foreign_key_checks = 0;
S: stop slave io_thread;
S: stop slave;
S: reset slave;
S: reset master;
S: set time_zone = '+00:00';
_: (for each database:)
S:__ show databases like '$db';
_:__ (if it does not exist:)
S:____ create database $db;
_:__ (for each table:)
M:____ show create table $db.$table;
S:____ show create table $db.$table;
_:____ (if different:)
S:______ drop table $db.$table;
_:____ (if different or not on slave:)
S:______ create table $db.$table .... ;
S:____ delete from $db.$table;
M:____ select * from $db.$table;
S:____ insert into $db.$table values ([...from above...]);
S:__ show tables in $db;
_:__ (for each table that shouldn't be there:)
S:____ drop table $db.$table;
S: show databases;
_: (for each db that shouldn't be there:)
S:__ drop database $db;
S: change master to [...details from show master above...];
S: start slave io_thread;
S: start slave;
M: rollback;
(Urghh... I see no way to embed code into these posts. Hence the ugly "____" thing to indicate indentation. I suggest copying into something with a non-proportinal font, to make it easier to read)
Be Safe, target your SQL for the Unix standard of "LowerCase" and you will be a happy developer/administrator!
MySQL Failover Circular Replication
===================================
Assume we have 2 servers: Server1 and Server2.
Server1 Settings
================
1. Put the option file my.cnf to Server1 path /etc with these settings:
[mysqld]
log-bin=mysql-bin
server-id = 1
auto_increment_increment = 10
auto_increment_offset = 1
2. Change mode/permission for my.cnf to _rw_r__r__ else mysql will igonore it.
sudo chmod 644 my.cnf
3. Stop and start mysql.
cd /Library/StartupItems/MySQLCOM
sudo ./MySQLCOM stop
sudo ./MySQLCOM start
4. Configure the server:
# create a user for replication process:
cd /usr/local/mysql/bin
./mysql -p -u root
create user replicant@'%' identified by 'password';
# Grant access rights:
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
Flush Privileges;
# Specify the info for the serve2:
CHANGE MASTER TO
MASTER_HOST='ip_of_server2',
MASTER_USER='replication_user_name_on_server2',
MASTER_PASSWORD='replication_password_on_server2';
# Start the listerner:
Start slave;
# Verify whether the replication is working:
show slave status\G
Server2 Settings
================
1. Put the option file my.cnf on to Server2 path /etc
with these settings:
[mysqld]
log-bin=mysql-bin
server-id = 2
auto_increment_increment = 10
auto_increment_offset = 2
2. Change mode/permission for my.cnf to _rw_r__r__ else mysql will igonore it.
sudo chmod 644 my.cnf
3. Stop and start mysql.
cd /Library/StartupItems/MySQLCOM
sudo ./MySQLCOM stop
sudo ./MySQLCOM start
4. Configure the server:
# create a user for replication process:
cd /usr/local/mysql/bin
./mysql -p -u root
create user replicant@'%' identified by 'password';
# Grant access rights:
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
Flush Privileges;
# Specify the info for the serve1:
CHANGE MASTER TO
MASTER_HOST='ip_of_server1',
MASTER_USER='replication_user_name_on_server1',
MASTER_PASSWORD='replication_password_on_server1';
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1',
# MASTER_USER='replicant', MASTER_PASSWORD='password';
# Load data from Server1:
Load Data from Master;
# Start the listerner:
Start slave;
What will happen in a two-node solution with circular replication as described at http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html is that the update is copied to the other node while the local update is overwritten.
This is a flaw that has to be considered, and the right way to resolve this is probably through the MySQL Cluster.
Basically you just need to setup the server id, create a user with the right privilege and start the replication:
http://blog.bottomlessinc.com/2010/03/setting-up-mysql-replication/
The doc was confusing to me and not in chronological order.
http://mysql-performance-1-find-1.blogspot.in/2012/02/master-slave-master-master-replication.html