mysql 自动复制_MySQL 复制搭建(附mysql自动搭建脚本)

# MySQL 复制搭建(附mysql自动搭建脚本)

1. 用自动化脚本拉起一个mysql数据库，作为主库

`[root@upright92 mysql]# ./install_mysql.py 3306`

2.  连接到主库上，创建复制用户repl

```mysql> alter user user() identified by '';

Query OK, 0 rows affected (0.00 sec)

mysql> create user repl identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to repl;

Query OK, 0 rows affected (0.00 sec)

```

3.  在主库上加载数据

```

[root@upright92 data]# mysqlslap --user=root --socket=/tmp/mysql3306.sock  --concurrency=10 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql -p --no-drop

```

4. 在主库上备份数据

```

[root@upright92 backup]# mysqldump -uroot -p -S /tmp/mysql3306.sock -A --single-transaction --master-data=2 > full_backup_3306_1351.sql

[root@upright92 backup]# ls -ltr

-rw-r--r-- 1 root root 1201471 Jul 11 13:52 full_backup_3306_1351.sql

```

5. 在从库上拉起mysql数据库，导入主库上的备份文件

```

[root@upright91 mysql]# ./install_mysql.py 3307

[root@upright91 data]# cat error.log |grep pass

2019-07-11T05:56:35.409964Z 1 [Note] A temporary password is generated for root@localhost: NTh1GAby;*_W

[root@upright91 data]# mysql -S /tmp/mysql3307.sock -uroot -p

root@localhost [(none)]>alter user user() identified by '';

root@localhost [(none)]>reset master;  --一定要注意， 需要在从库上reset master

root@localhost [(none)]>source full_backup_3306_1351.sql

```

6. 设置复制

```

change master to  \

master_host='192.168.0.92', \

master_user='repl' , \

master_password='123456', \

master_port=3306 ,\

master_auto_position=1;

start slave;  --启动复制

```

7. 确认复制状态

```

root@localhost [mc]>show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.92

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 2261804

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 998

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 2261804

Relay_Log_Space: 1199

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 923306

Master_UUID: 6f3cc46a-a38b-11e9-874a-6c92bf2b6cf6

Master_Info_File: /data/mysql/mysql3307/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 6f3cc46a-a38b-11e9-874a-6c92bf2b6cf6:797-799

Executed_Gtid_Set: 6f3cc46a-a38b-11e9-874a-6c92bf2b6cf6:1-799

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

```

## 主库config文件

```

#my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

[mysql]

prompt="\\u@\\h [\\d]>"

no-auto-rehash

[mysqld]

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql3306/data

port = 3306

socket = /tmp/mysql3306.sock

event_scheduler = 0

tmpdir = /data/mysql/mysql3306/tmp

#timeout

interactive_timeout = 300

wait_timeout = 300

#character set

character-set-server = utf8

open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000

#lower_case_table_names =1

#logs

log-output=file

slow_query_log = 1

slow_query_log_file = slow.log

log-error = error.log

log_warnings = 2

pid-file = mysql.pid

long_query_time = 1

log-slow-slave-statements = 1

binlog_format = row

server-id = 923306

log-bin = /data/mysql/mysql3306/logs/mysql-bin

binlog_cache_size = 4M

max_binlog_size = 256M

max_binlog_cache_size = 1M

sync_binlog = 0

expire_logs_days = 10

#procedure

log_bin_trust_function_creators=1

#

gtid-mode = on

enforce_gtid_consistency=on

#relay log

skip_slave_start = 1

max_relay_log_size = 128M

relay_log_purge = 1

relay_log_recovery = 1

relay-log=relay-bin

relay-log-index=relay-bin.index

log_slave_updates

#slave-skip-errors=1032,1053,1062

#skip-grant-tables

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 128K

join_buffer_size = 128K

thread_cache_size = 200

query_cache_size = 0

query_cache_type = 0

query_cache_limit = 256K

query_cache_min_res_unit = 512

thread_stack = 192K

tmp_table_size = 96M

key_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 32M

#myisam

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

#innodb

innodb_buffer_pool_size = 100M

innodb_buffer_pool_instances = 1

innodb_data_file_path = ibdata1:100M:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 8M

innodb_log_file_size = 100M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 50

innodb_file_per_table = 1

innodb_rollback_on_timeout

innodb_status_file = 1

innodb_io_capacity = 2000

transaction_isolation = READ-COMMITTED

innodb_flush_method = O_DIRECT

```

## 从库配置文件

```

#my.cnf

[client]

port = 3307

socket = /tmp/mysql.sock

[mysql]

prompt="\\u@\\h [\\d]>"

no-auto-rehash

[mysqld]

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql3307/data

port = 3307

socket = /tmp/mysql3307.sock

event_scheduler = 0

tmpdir = /data/mysql/mysql3307/tmp

#timeout

interactive_timeout = 300

wait_timeout = 300

#character set

character-set-server = utf8

open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000

#lower_case_table_names =1

#logs

log-output=file

slow_query_log = 1

slow_query_log_file = slow.log

log-error = error.log

log_warnings = 2

pid-file = mysql.pid

long_query_time = 1

log-slow-slave-statements = 1

binlog_format = row

server-id = 123307

log-bin = /data/mysql/mysql3307/logs/mysql-bin

binlog_cache_size = 4M

max_binlog_size = 256M

max_binlog_cache_size = 1M

sync_binlog = 0

expire_logs_days = 10

#procedure

log_bin_trust_function_creators=1

#

gtid-mode = on

enforce_gtid_consistency=on

#relay log

skip_slave_start = 1

max_relay_log_size = 128M

relay_log_purge = 1

relay_log_recovery = 1

relay-log=relay-bin

relay-log-index=relay-bin.index

log_slave_updates

#slave-skip-errors=1032,1053,1062

#skip-grant-tables

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 128K

join_buffer_size = 128K

thread_cache_size = 200

query_cache_size = 0

query_cache_type = 0

query_cache_limit = 256K

query_cache_min_res_unit = 512

thread_stack = 192K

tmp_table_size = 96M

key_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 32M

#myisam

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

#innodb

innodb_buffer_pool_size = 100M

innodb_buffer_pool_instances = 1

innodb_data_file_path = ibdata1:100M:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 8M

innodb_log_file_size = 100M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 50

innodb_file_per_table = 1

innodb_rollback_on_timeout

innodb_status_file = 1

innodb_io_capacity = 2000

transaction_isolation = READ-COMMITTED

innodb_flush_method = O_DIRECT

```

## 自动化安装脚本 install_mysql.py

安装路径: /data/mysql/mysql${port}/{tmp,logs,data}

mysql安装包路径: /user/local/mysql

脚本调用方法: ./install_mysql.py ${port}

```

[root@upright92 mysql]# cat install_mysql.py

#!/usr/bin/python

import os

import os.path

import sys

import socket

PORT=sys.argv[1]

IP=socket.gethostbyname(socket.gethostname())

IP=IP[IP.rfind('.')+1:]

SERVER_ID=IP+PORT

##auto start mysql

### mysql configure file

my_conf="""

#my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

[mysql]

prompt="\\\\u@\\\\h [\\\\d]>"

no-auto-rehash

[mysqld]

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql3306/data

port = 3306

socket = /tmp/mysql3306.sock

event_scheduler = 0

tmpdir = /data/mysql/mysql3306/tmp

#timeout

interactive_timeout = 300

wait_timeout = 300

#character set

character-set-server = utf8

open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000

#lower_case_table_names =1

#logs

log-output=file

slow_query_log = 1

slow_query_log_file = slow.log

log-error = error.log

log_warnings = 2

pid-file = mysql.pid

long_query_time = 1

log-slow-slave-statements = 1

binlog_format = row

server-id = 123306

log-bin = /data/mysql/mysql3306/logs/mysql-bin

binlog_cache_size = 4M

max_binlog_size = 256M

max_binlog_cache_size = 1M

sync_binlog = 0

expire_logs_days = 10

#procedure

log_bin_trust_function_creators=1

#

gtid-mode = on

enforce_gtid_consistency=on

#relay log

skip_slave_start = 1

max_relay_log_size = 128M

relay_log_purge = 1

relay_log_recovery = 1

relay-log=relay-bin

relay-log-index=relay-bin.index

log_slave_updates

#slave-skip-errors=1032,1053,1062

#skip-grant-tables

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 128K

join_buffer_size = 128K

thread_cache_size = 200

query_cache_size = 0

query_cache_type = 0

query_cache_limit = 256K

query_cache_min_res_unit = 512

thread_stack = 192K

tmp_table_size = 96M

key_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 32M

#myisam

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

#innodb

innodb_buffer_pool_size = 100M

innodb_buffer_pool_instances = 1

innodb_data_file_path = ibdata1:100M:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 8M

innodb_log_file_size = 100M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 50

innodb_file_per_table = 1

innodb_rollback_on_timeout

innodb_status_file = 1

innodb_io_capacity = 2000

transaction_isolation = READ-COMMITTED

innodb_flush_method = O_DIRECT

"""

BASE_DIR="/data/mysql/mysql"+str(PORT)

DATA_DIR="/data/mysql/mysql"+str(PORT)+"/data"

LOG_DIR="/data/mysql/mysql"+str(PORT)+"/logs"

TMP_DIR="/data/mysql/mysql"+str(PORT)+"/tmp"

parameter="/data/mysql/mysql"+str(PORT)+"/my"+str(PORT)+".cnf"

my_conf=my_conf.replace('3306',PORT)

my_conf=my_conf.replace('123306',SERVER_ID)

#if data dir exsits

if os.path.isdir(BASE_DIR):

print(str(PORT) + " exits, please change")

else:

os.mkdir(BASE_DIR)

os.mkdir(DATA_DIR)

os.mkdir(LOG_DIR)

os.mkdir(TMP_DIR)

os.system("chown -R mysql.mysql "+BASE_DIR)

with open(parameter,'w') as f:

f.writelines(my_conf)

os.system("mysqld --defaults-file="+parameter+" --initialize")

os.system("mysqld --defaults-file="+parameter+" &")

```