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+" &")

```

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 218,036评论 6赞 506
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 93,046评论 3赞 395
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 164,411评论 0赞 354
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,622评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,661评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,521评论 1赞 304
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,288评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,200评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,644评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,837评论 3赞 336
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,953评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,673评论 5赞 346
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,281评论 3赞 329
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,889评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,011评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,119评论 3赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,901评论 2赞 355