mysql replication

最新推荐文章于 2021-03-17 05:41:35 发布

fanchuan0077

最新推荐文章于 2021-03-17 05:41:35 发布

阅读量852

点赞数

分类专栏： Techs 文章标签： mysql file buffer ssl database socket

本文链接：https://blog.csdn.net/fanchuan0077/article/details/5516420

版权

Techs 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

MySql has its own user:group, which typically is mysql:mysql.

Setup Master

Add mysql account on the master server

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_host_ip' IDENTIFIED BY 'slavepass';

#Then slave can access master with master_user=’repl’ and master_password='slavepass'’’.

Flush all the tables and block write statements

mysql> FLUSH TABLES WITH READ LOCK;

#Start a new mysql client and keep the running client from which you issued the flush tables, #because it can be unlocked automatically at the close of the client.

#For InnoDB, additionally the #mysql should be shutdown to insure a consistent snapshot for InnoDB tables.

Remember the master_log_file and master_log_pos which are needed by slaves.

mysql > SHOW MASTER STATUS;

Make a snapshot of all the databases(Or just the databases which will be synchronized with slaves)

Cd /usr/local/mytools/repository/mysql_version/data

#data is where the database data are stored, if no data directory, there should be var directory.

tar -cvf /tmp/mysql-snapshot.tar .

mysql>unlock tables;

Modify my.cnf

Cd /usr/local/mytools/repository/mysql_5.0.51a

Vim my.cnf

# Add the following lines right bellow [client]

socket = /local/tools/repository/mysql-5.0.51a/tmp/mysql.sock

port = 3306

# Add the following lines right below [mysqld]

socket = /local/tools/repository/mysql-5.0.51a/tmp/mysql.sock

port = 3306

sync_binlog = 1 #enable update statements log

log-bin=/usr/local/mytools/repository/mysql-5.0.51a/data/mysql-bin #set log file name

binlog-do-db = replication_dbname1 #set all the replication databases

binlog-do-db = replication_dbname2

slave_skip_errors=all

service_id=n # ‘n’ is a positive integer unique among master and slaves

user=mysql

#For InnoDB, additionally the following lines will be added.

innodb_data_home_dir = /usr/local/mytools/repository/mysql-5.0.51a/data/

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /usr/local/mytools/repository/mysql-5.0.51a/data/

innodb_log_arch_dir = /usr/local/mytools/repository/mysql-5.0.51a/data/

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 16M

innodb_additional_mem_pool_size = 2M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 5M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

#After the preceding procedures, We can restart MySql for master.

Check master

mysql> show master status /G;

*************************** 1. row ***************************

File: mysql-bin.000022

Position: 774

Binlog_Do_DB: gisus,syn_test

Binlog_Ignore_DB:

1 row in set (0.00 sec)

ERROR:

No query specified

Setup Slave

Stop server

Cd /usr/local/mytools/repository/mysql_5.0.51a/bin

./mysqladmin –u username –ppassword shutdown

Modify my.cnf

Cd /usr/local/mytools/repository/mysql_5.0.51a

Vim my.cnf

#Add the following lines right below [client]

socket = /local/tools/repository/mysql-5.0.51a/tmp/mysql.sock

port = 3306

#Add the following lines right below [mysqld]

socket = /local/tools/repository/mysql-5.0.51a/tmp/mysql.sock

port = 3306

log-bin=/usr/local/mytools/repository/mysql-5.0.51a/data/mysql-bin

replicate-do-db = replication_dbname1 #set all the replication databases by this

replicate-do-db = replication_dbname2

slave_skip_errors=all

sync_binlog = 1

service_id=n # ‘n’ is positive integer unique among master and slaves

user=mysql

# The replication master for this slave - required

master-host = master_host_ip

# The account the slave will use for authentication when connecting

# to the master,which is created previously be “Grant…” in master.

master-user = repl

master-password = slavepass

# The port the master is listening on.

# optional - defaults to 3306

master-port = 3306

# binary logging - not required for slaves, but recommended

log-bin=/local/tools/repository/mysql-5.0.51a/data/mysql-bin

replicate-do-db = replication_dbname1 #indicate databases you want to synchronize.

replicate-do-db = replication_dbname2

replicate-ignore-table = replication_dbname1.tablename_ignore. #Indicate tables not been synchronized inside the specified databases

replicate-ignore-table = replication_dbname2.tablename_ignore

log-slave-updates

slave-skip-errors=all

read-only #Insure the db is only updated by slave thread.

#For InnoDB, additionally the following lines will be added.

innodb_data_home_dir = /local/tools/repository/mysql-5.0.51a/data/

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /local/tools/repository/mysql-5.0.51a/data/

innodb_log_arch_dir = /local/tools/repository/mysql-5.0.51a/data/

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 16M

innodb_additional_mem_pool_size = 2M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 5M

#innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

Copy database snapshot mysql-snapshot.tar to /tmp/

Scp root@master_host_ip: /tmp/mysql-snapshot.tar /tmp/

Unzip snapshot into local database data directory.

Cd /local/tools/repository/mysql-5.0.51a/data/

Tar –xvf /tmp/mysql-snapshot.tar

#Make sure mysql has the access to data/* and my.cnf

Cd /local/tools/repository/mysql-5.0.51a/

Chown mysql:mysql *

Cd /local/tools/repository/mysql-5.0.51a/data/

Chown mysql:mysql *

Start mysql without slave

Service mysql start --skip-slave-start

Change slave status

Mysql>change master to

->master_log_file=’master_log_file’,

->master_log_pos=master_log_pos;

#Here we can change more, such as

#->master_host=’master_host’,

#->master_user=’repl’,

#->master_password=’slavepass’,

#->master_port=’3306’,

#but since all the others are correctly configured in my.cnf, no need to do it again.

Start slave and check the slave status.

Mysql>slave start;

Mysql>show slave status /G;

#if it appears as follow, then it’s ok.

# Slave_IO_State: Waiting for master to send event #attention

# Master_Host: master_host_ip e.g 172,24,61,58 #attention

# Master_User: repl #attention

# Master_Port: 3306 #attention

# Connect_Retry: 10

# Master_Log_File: master_log_file e.g mysql-bin.000022 #attention

#Read_Master_Log_Pos: master_log_pos, e.g 774 #attention

# Relay_Log_File: e.g aclgcl-134-relay-bin.000087

# Relay_Log_Pos: 235

#Relay_Master_Log_File: master_log_file e.g mysql-bin.000022 #attention

# Slave_IO_Running: Yes #attention

# Slave_SQL_Running: Yes #attention

# Replicate_Do_DB: replication_dbnames #attention

# Replicate_Ignore_DB:

# Replicate_Do_Table:

#Replicate_Ignore_Table: replication_ignore_tables #attention

#Replicate_Wild_Do_Table:

#Replicate_Wild_Ignore_Table:

# Last_Errno: 0

# Last_Error:

# Skip_Counter: 0

#Exec_Master_Log_Pos: 678

# Relay_Log_Space: 235

# Until_Condition: None

# Until_Log_File:

# Until_Log_Pos: 0

# Master_SSL_Allowed: No

# Master_SSL_CA_File:

# Master_SSL_CA_Path:

# Master_SSL_Cert:

# Master_SSL_Cipher:

# Master_SSL_Key:

#Seconds_Behind_Master: 0

Make a salve server also a master

Modify my.cnf

Add the following lines to my.cnf right bellow [mysqld], then restart the mysql.

binlog-do-db = replication_dbname1 #indicate databases you want to synchronize.

binlog-do-db = replication_dbname2

sync_binlog = 1

Restart the mysql

/etc/rc.d/init.d/mysql restart

Create ssh tunneling, as follow, which means routing all requests to local port 33008 to remote server root@sumaster.tctmobile.com on port 127.0.0.1:33007, we can take sumaster.tctmobile.com as a proxy here.

ssh -p 18082 -N -f -L 33008:127.0.0.1:33007 root@sumaster.tctmobile.com