MySQL雙主+keeplived安裝部署說明

来源:http://www.cnblogs.com/bjx2020/archive/2017/12/15/8041465.html
-Advertisement-
Play Games

MySQL雙主+keeplived安裝部署說明 一、環境介紹 1.1、規劃 序號 類別 版本 主機名 IP 埠 備註 1 OS CentOS release 6.9 (Final) (minimal) my1 172.16.210.180 8306 172.16.210.183 2 mysql m ...


MySQL雙主+keeplived安裝部署說明

一、環境介紹

1.1、規劃

序號

類別

版本

主機名

IP

埠

備註

1

OS

CentOS release 6.9 (Final) (minimal)

my1

172.16.210.180

8306

 

172.16.210.183

2

mysql

mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

my2

172.16.210.181

8306

3

keeplived

keepalived-1.2.7.tar.gz

 

 

 

 

 

參考資料：

http://www.cnblogs.com/276815076/p/5649539.html

 

mysql下載地址：

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

 

keeplived下載地址：

http://www.keepalived.org/software/keepalived-1.2.7.tar.gz

1.2、MySQL雙主互備+keepalived高可用架構介紹

　　MySQL主從複製架構可以在很大程度保證MySQL的高可用，在一主多從的架構中還可以利用讀寫分離將讀操作分配到從庫中，減輕主庫壓力。但是在這種架構中，主庫出現故障時需要手動將一臺從庫提升為主庫。在對寫操作要求較高的環境中，主庫故障在主從架構中會成為單點故障。因此需要主主互備架構，避免主節點故障造成寫操作失效。

　　在雙主互備的架構中，每台MySQL都充當主伺服器，同時充當對方的從伺服器。在任意一臺伺服器上的寫操作都會被覆制到另一臺伺服器上，從而保證了數據的可靠性。

　　在雙主互備的基礎上加上keepalived，在其中一臺機器上綁定虛擬ip（VIP）。利用vip統一對外服務，可以避免在兩個節點同時寫數據造成衝突。同時當keepalived主節點發生故障時，keeplived會自動將VIP切換到備節點上，從而實現主伺服器的高可用。

 

二、安裝mysql5.7

2.1、主機M1上的操作

2.1.1、安裝依賴包

yum  clean all

yum -y update

yum -y install gcc gcc-c++ make autoconf automake ncurses-devel bison ncurses  cmake libaio libaio-devel  boost

yum -y install gcc-c++ gd libxml2-devel libjpeg-devel libpng-devel net-snmp-devel wget telnet vim zip unzip

yum -y install curl-devel libxslt-devel pcre-devel libjpeg libpng libcurl4-openssl-dev

yum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxslt

yum -y install gcc automake autoconf libtool openssl-devel

yum -y install perl-devel perl-ExtUtils-Embed

yum -y install cmake ncurses-devel.x86_64  openldap-devel.x86_64 lrzsz  openssh-clients gcc-g77  bison

yum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-devel

yum -y install ntpdate rsync svn  patch  iptables iptables-services

yum -y install libevent libevent-devel  cyrus-sasl cyrus-sasl-devel

yum -y install gd-devel libmemcached-devel memcached git libssl-devel libyaml-devel auto make

yum -y groupinstall "Server Platform Development" "Development tools"

yum -y groupinstall "Development tools"

 

2.1.2、下載解壓

rm -rf /etc/my.cnf  

mkdir -p /opt/mysql

cd /opt/mysql/

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

tar -zxf  mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

cd /usr/local/

ln -s /opt/mysql/mysql-5.7.20-linux-glibc2.12-x86_64  mysql

 

 

2.1.3、創建所需要的目錄

mkdir  -p /data/mysql/mysql_8306/{data,logs,tmp}

2.1.4、更改許可權

groupadd mysql

useradd -g mysql mysql -d /home/mysql -s /sbin/nologin

2.1.5、創建my.cnf

cat >/data/mysql/mysql_8306/my_8306.cnf <<EOF

#my.cnf

[client]

port            = 8306

socket          = /data/mysql/mysql_8306/tmp/mysql_8306.sock

 

[mysql]

#prompt="\u@\h:\p  \R:\m:\s [\d]>"

#tee=/data/mysql/mysql_8306/data/query.log

#prompt="\u@\h:\p  \R:\m:\s [\d]>"

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

connect_timeout = 5

no-auto-rehash

 

[mysqld]

#misc

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql_8306/data

port = 8306

socket = /data/mysql/mysql_8306/tmp/mysql_8306.sock

 

#timeout

interactive_timeout = 300

wait_timeout = 300

 

#character set

character-set-server = utf8

 

open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000

 

skip-name-resolve = 1

#logs

log-output=file

slow_query_log = 1

slow_query_log_file = /data/mysql/mysql_8306/logs/slow.log

log-error = /data/mysql/mysql_8306/logs/error.log

log_error_verbosity = 3

pid-file = mysql.pid

long_query_time = 1

#log-slow-admin-statements = 1

#log-queries-not-using-indexes = 1

log-slow-slave-statements = 1

 

#tmp

tmpdir=/data/mysql/mysql_8306/tmp

event_scheduler = 1

performance_schema = on

max_allowed_packet = 32M

character_set_server = utf8mb4

#character_set_server = utf8

default-time-zone = system

default-storage-engine = InnoDB

#bind_address = 172.16.151.248

explicit_defaults_for_timestamp = 1

 

 

#binlog

binlog_format = row

server-id = 1818306

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

log-bin-index = /data/mysql/mysql_8306/logs/mysql-bin.index

binlog_cache_size = 4M

max_binlog_size = 1G

max_binlog_cache_size = 2G

sync_binlog = 0

expire_logs_days = 90

#replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

 

#relay log

skip_slave_start = 1

max_relay_log_size = 1G

relay_log_purge = 1

relay_log_recovery = 1

log_slave_updates

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

 

explicit_defaults_for_timestamp=1

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 2M

join_buffer_size = 2M

thread_cache_size = 256

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 = 10G

innodb_buffer_pool_instances = 1

innodb_data_file_path = ibdata1:1G:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 64M

innodb_log_file_size = 500M

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

gtid_mode = ON

enforce_gtid_consistency = ON

master_info_repository = TABLE

relay-log-info-repository = TABLE

binlog_checksum = NONE

log_slave_updates = ON

 

# Two-Master configure

#server-1

auto-increment-offset = 1

auto-increment-increment = 2

 

#server-2                          

#auto-increment-offset = 2

#auto-increment-increment = 2

 

# semi sync replication settings #

plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路徑

#plugin_dir = /usr/local/mysql/lib/mysql/plugin

plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路徑

#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

 

loose_rpl_semi_sync_master_enabled = on     

loose_rpl_semi_sync_master_timeout = 5000      

loose_rpl_semi_sync_master_trace_level = 32   

loose_rpl_semi_sync_master_wait_no_slave = on

loose_rpl_semi_sync_slave_enabled = on      

loose_rpl_semi_sync_slave_trace_level = 32

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

loose_rpl_semi_sync_master_wait_for_slave_count=1

loose_rpl_semi_sync_master_wait_point=AFTER_SYNC

 

slave_preserve_commit_order = 1

slave_transaction_retries = 128

log_timestamps = system

show_compatibility_56 = on

slave_parallel_workers = 16

slave_parallel_type = LOGICAL_CLOCK

 

loose_innodb_numa_interleave = 1

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 16

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 2G

innodb_purge_rseg_truncate_frequency = 128

#transaction_write_set_extraction = MURMUR32

 

 

# group replication

##log-bin = mysql

##server-id = 618306

##gtid_mode = ON

##enforce_gtid_consistency = ON

##master_info_repository = TABLE

##relay-log-info-repository = TABLE

##binlog_checksum = NONE

##log_slave_updates = ON

##binlog_format = row

##transaction_write_set_extraction=XXHASH64

##loose-group_replication_group_name = '3db33b36-0e51-409f-a61d-c99756e90154'

##loose-group_replication_start_on_boot = off

##loose-group_replication_local_address= "10.125.141.62:28306"  # 不能超過5位數字

##loose-group_replication_group_seeds= "10.125.141.62:28306,10.125.141.62:23307,10.125.141.62:23308"  # 不能超過5位數字

##loose-group_replication_bootstrap_group= off

# loose-group_replication_single_primary_mode=FALSE   ###本次搭建的是mutil_mode

# loose-group_replication_enforce_update_everywhere_checks= TRUE

 

[mysqld_safe]

#malloc-lib=/usr/local/mysql/lib/jmalloc.so

nice=-19

open-files-limit=65535

EOF

2.1.6、修改目錄許可權

chown -R mysql.mysql /data/mysql/

2.1.7、初始化

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --initialize-insecure &

2.1.8、啟動

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &

 

#  推薦的啟動方式

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &

 

 

2.1.9、登陸方式

/usr/local/mysql/bin/mysql  -uroot -p -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock

或者

/usr/local/mysql/bin/mysql  -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock

 

2.1.10、創建授權修改密碼

set sql_log_bin = 0;

create user 'rpl_user'@'%';

grant replication slave on *.* to 'rpl_user'@'%' identified by 'rpl_user2017';

update mysql.user set authentication_string=password('root2017') where user='root';

flush privileges;

set sql_log_bin = 1;

reset  master ; reset slave all;

 

2.1.11、change master

CHANGE MASTER TO MASTER_HOST='172.16.210.181',MASTER_USER='rpl_user',

MASTER_PASSWORD='rpl_user2017',MASTER_PORT=8306,

MASTER_CONNECT_RETRY=10,MASTER_AUTO_POSITION =1;

 

start slave;

show slave status\G;

 

 

2.1.12、設置快捷登陸方式

[root@my1 local]# /usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=8306_localhost_login \

> --user=root --port=8306  --password --socket=/data/mysql/mysql_8306/tmp/mysql_8306.sock

Enter password:

[root@my1 local]#

 
# 查看

/usr/local/mysql/bin/mysql_config_editor print --all

 

[root@my1 local]# alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'

 

2.1.13、快捷關閉資料庫

[root@my1 local]#alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'

 

2.1.14、加入備忘錄

[root@my1 ~]# cat  >>/root/.bashrc <<EOF

alias mysql.8306.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &'

alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'

alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'

EOF


[root@my1 ~]# source /root/.bash_profile

 

2.1.15、備份腳本

[root@my1 ~]# cat /root/all_database.sh

#!/bin/bash

/usr/local/mysql/bin/mysqldump --login-path=8306_localhost_login -R -E --triggers -e --max_allowed_packet=16777216 --net_buffer_length=16384  --master-data=2 --single-transaction --all-databases --quick  | gzip >/root/all_database_bak_`date +%Y-%m-%d_%H_%M_%S`.sql.gz

[root@my1 ~]#

 

2.2、主機M2上的操作

2.2.1、安裝依賴包

yum  clean all

yum -y update

yum -y install gcc gcc-c++ make autoconf automake ncurses-devel bison ncurses  cmake libaio libaio-devel  boost

yum -y install gcc-c++ gd libxml2-devel libjpeg-devel libpng-devel net-snmp-devel wget telnet vim zip unzip

yum -y install curl-devel libxslt-devel pcre-devel libjpeg libpng libcurl4-openssl-dev

yum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxslt

yum -y install gcc automake autoconf libtool openssl-devel

yum -y install perl-devel perl-ExtUtils-Embed

yum -y install cmake ncurses-devel.x86_64  openldap-devel.x86_64 lrzsz  openssh-clients gcc-g77  bison

yum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-devel

yum -y install ntpdate rsync svn  patch  iptables iptables-services

yum -y install libevent libevent-devel  cyrus-sasl cyrus-sasl-devel

yum -y install gd-devel libmemcached-devel memcached git libssl-devel libyaml-devel auto make

yum -y groupinstall "Server Platform Development" "Development tools"

yum -y groupinstall "Development tools"

2.2.2、下載安裝

rm -rf /etc/my.cnf  

mkdir -p /opt/mysql

cd /opt/mysql/

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

tar -zxf  mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

cd /usr/local/

ln -s /opt/mysql/mysql-5.7.20-linux-glibc2.12-x86_64  mysql

2.2.3、創建所需要的目錄

mkdir  -p /data/mysql/mysql_8306/{data,logs,tmp} 

2.2.4、更改許可權

groupadd mysql

useradd -g mysql mysql -d /home/mysql -s /sbin/nologin

 

 

2.2.5、創建my.cnf

cat >/data/mysql/mysql_8306/my_8306.cnf <<EOF

#my.cnf

[client]

port            = 8306

socket          = /data/mysql/mysql_8306/tmp/mysql_8306.sock

 

[mysql]

#prompt="\u@\h:\p  \R:\m:\s [\d]>"

#tee=/data/mysql/mysql_8306/data/query.log

#prompt="\u@\h:\p  \R:\m:\s [\d]>"

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

connect_timeout = 5

no-auto-rehash

 

[mysqld]

#misc

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql_8306/data

port = 8306

socket = /data/mysql/mysql_8306/tmp/mysql_8306.sock

 

#timeout

interactive_timeout = 300

wait_timeout = 300

 

#character set

character-set-server = utf8

 

open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000

 

skip-name-resolve = 1

#logs

log-output=file

slow_query_log = 1

slow_query_log_file = /data/mysql/mysql_8306/logs/slow.log

log-error = /data/mysql/mysql_8306/logs/error.log

log_error_verbosity = 3

pid-file = mysql.pid

long_query_time = 1

#log-slow-admin-statements = 1

#log-queries-not-using-indexes = 1

log-slow-slave-statements = 1

 

#tmp

tmpdir=/data/mysql/mysql_8306/tmp

event_scheduler = 1

performance_schema = on

max_allowed_packet = 32M

character_set_server = utf8mb4

#character_set_server = utf8

default-time-zone = system

default-storage-engine = InnoDB

#bind_address = 172.16.151.248

explicit_defaults_for_timestamp = 1

 

 

#binlog

binlog_format = row

server-id = 1818306

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

log-bin-index = /data/mysql/mysql_8306/logs/mysql-bin.index

binlog_cache_size = 4M

max_binlog_size = 1G

max_binlog_cache_size = 2G

sync_binlog = 0

expire_logs_days = 90

#replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

 

 

#relay log

skip_slave_start = 1

max_relay_log_size = 1G

relay_log_purge = 1

relay_log_recovery = 1

log_slave_updates

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

 

explicit_defaults_for_timestamp=1

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 2M

join_buffer_size = 2M

thread_cache_size = 256

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 = 10G

innodb_buffer_pool_instances = 1

innodb_data_file_path = ibdata1:1G:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 64M

innodb_log_file_size = 500M

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

gtid_mode = ON

enforce_gtid_consistency = ON

master_info_repository = TABLE

relay-log-info-repository = TABLE

binlog_checksum = NONE

log_slave_updates = ON

 

# Two-Master configure

#server-1

#auto-increment-offset = 1

#auto-increment-increment = 2

 

#server-2                          

auto-increment-offset = 2

auto-increment-increment = 2

 

# semi sync replication settings #

plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路徑

#plugin_dir = /usr/local/mysql/lib/mysql/plugin

plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路徑

#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

 

loose_rpl_semi_sync_master_enabled = on     

loose_rpl_semi_sync_master_timeout = 5000      

loose_rpl_semi_sync_master_trace_level = 32   

loose_rpl_semi_sync_master_wait_no_slave = on

loose_rpl_semi_sync_slave_enabled = on      

loose_rpl_semi_sync_slave_trace_level = 32

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

loose_rpl_semi_sync_master_wait_for_slave_count=1

loose_rpl_semi_sync_master_wait_point=AFTER_SYNC

 

slave_preserve_commit_order = 1

slave_transaction_retries = 128

log_timestamps = system

show_compatibility_56 = on

slave_parallel_workers = 16

slave_parallel_type = LOGICAL_CLOCK

 

loose_innodb_numa_interleave = 1

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 16

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 2G

innodb_purge_rseg_truncate_frequency = 128

#transaction_write_set_extraction = MURMUR32

 

 

# group replication

##log-bin = mysql

##server-id = 618306

##gtid_mode = ON

##enforce_gtid_consistency = ON

##master_info_repository = TABLE

##relay-log-info-repository = TABLE

##binlog_checksum = NONE

##log_slave_updates = ON

##binlog_format = row

##transaction_write_set_extraction=XXHASH64

##loose-group_replication_group_name = '3db33b36-0e51-409f-a61d-c99756e90154'

##loose-group_replication_start_on_boot = off

##loose-group_replication_local_address= "10.125.141.62:28306"  # 不能超過5位數字

##loose-group_replication_group_seeds= "10.125.141.62:28306,10.125.141.62:23307,10.125.141.62:23308"  # 不能超過5位數字

##loose-group_replication_bootstrap_group= off

# loose-group_replication_single_primary_mode=FALSE   ###本次搭建的是mutil_mode

# loose-group_replication_enforce_update_everywhere_checks= TRUE

 

[mysqld_safe]

#malloc-lib=/usr/local/mysql/lib/jmalloc.so

nice=-19

open-files-limit=65535

EOF

 

 

2.2.6、修改目錄許可權

chown -R mysql.mysql /data/mysql/

2.2.7、初始化

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --initialize-insecure & 

2.2.8、啟動

# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &


#  推薦的啟動方式

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &

 

2.2.9、登陸方式

/usr/local/mysql/bin/mysql  -uroot -p -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock


或者

/usr/local/mysql/bin/mysql  -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock

 

2.2.10、創建授權修改密碼

set sql_log_bin = 0;

create user 'rpl_user'@'%';

grant replication slave on *.* to 'rpl_user'@'%' identified by 'rpl_user2017';

update mysql.user set authentication_string=password('root2017') where user='root';

flush privileges;

set sql_log_bin = 1;

reset  master ; reset slave all;

 

 

2.2.11、change master

CHANGE MASTER TO MASTER_HOST='172.16.210.180',MASTER_USER='rpl_user',

MASTER_PASSWORD='rpl_user2017',MASTER_PORT=8306,

MASTER_CONNECT_RETRY=10,MASTER_AUTO_POSITION =1;

 

start slave;

show slave status\G;

 

 

2.2.12、設置快捷登陸方式

[root@my2 local]# /usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=8306_localhost_login \

> --user=root --port=8306  --password --socket=/data/mysql/mysql_8306/tmp/mysql_8306.sock

Enter password:

[root@my2 local]#

# 查看

/usr/local/mysql/bin/mysql_config_editor print --all

 

[root@my2 local]# alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'

2.2.13、快捷關閉資料庫

[root@my2 local]#alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'

2.2.14、加入備忘錄

[root@my2 ~]# cat  >>/root/.bashrc <<EOF

alias mysql.8306.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &'

alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'

alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'

EOF

[root@my2 ~]# source /root/.bash_profile

 

 

2.2.15、備份腳本

[root@my2 ~]# cat /root/all_database.sh

#!/bin/bash

/usr/local/mysql/bin/mysqldump --login-path=8306_localhost_login -R -E --triggers -e --max_allowed_packet=16777216 --net_buffer_length=16384  --master-data=2 --single-transaction --all-databases --quick  | gzip >/root/all_database_bak_`date +%Y-%m-%d_%H_%M_%S`.sql.gz

[root@my2 ~]#

 

三、驗證同步情況

3.1、在my1中操作

[root@my1 ~]# mysql.8306.login

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.20-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

[root@localhost][(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • SQL Server 2017 安裝過程中的一點說明（有點意思）
    會提到：“安裝程式無法與下載伺服器聯繫。請提供 Microsoft 機器學習伺服器安裝文件的位置，然後單擊“下一步”。可從以下位置下載安裝文件” 的解決方案 安裝過程和2016大體一致，機器學習這款更完善了。（其他錯誤看看往期的解決吧：http://www.cnblogs.com/dunitian/ ...
  • SQL Server 全文搜索
    SQL Server 的全文搜索(Full-Text Search）是基於分詞的文本檢索功能，依賴於全文索引。全文索引不同於傳統的平衡樹（B-Tree）索引和列存儲索引，它是由數據表構成的，稱作倒轉索引（Invert Index），存儲分詞和行的唯一鍵的映射關係。倒轉索引是在創建全文索引或更新全文索 ...
  • MapReduce多種join實現實例分析（一）
    一、概述 對於RDBMS中的join操作大伙一定非常熟悉，寫sql的時候要十分註意細節，稍有差池就會耗時巨久造成很大的性能瓶頸，而在Hadoop中使用MapReduce框架進行join的操作時同樣耗時，但是由於hadoop的分散式設計理念的特殊性，因此對於這種join操作同樣也具備了一定的特殊性。本 ...
  • 源碼中的哲學——通過構建者模式創建SparkSession
    spark2.2在使用的時候使用的是SparkSession，這個SparkSession創建的時候很明顯的使用了創建者模式。通過觀察源代碼，簡單的模擬了下，可以當作以後編碼風格的參考： 官方使用 自己寫的小例子，模擬一下： 很好的風格！ 可以得到輸出: ...
  • Mysql----關於內聯，左聯，右聯，全聯的使用和理解
    準備工作：新建兩張表 表一：student 填充內容：編號，姓名，班級 表二：school 填充內容：編號，班級，專業 這兩張表建好了，意為班級選課表，兩張表沒有任何主外鍵的關係，下麵進行內聯，左聯，右聯，全聯的實驗 一， 內聯接. 關鍵字：INNER JOIN 語法：SELECT * from 表 ...
  • hadoop日常維護之問題解決01
    執行hadoop任務遇到的問題： Caused by: org.apache.hadoop.ipc.RemoteException(java.io.IOException): File /user/hive/warehouse/rec_new2.db/user_session/.hive-stagi ...
  • [linux]centos7下解決yum install mysql-server沒有可用包
    第一步：安裝從網上下載文件的wget命令 第二步：下載mysql的repo源 第三步：安裝mysql-community-release-el7-5.noarch.rpm包 第四步：查看下 會獲得兩個mysql的yum repo源：/etc/yum.repos.d/mysql-community.r ...
  • [20171213]john破解oracle口令.txt
    [20171213]john破解oracle口令.txt--//跟別人討論的oracle破解問題,我曾經提過不要使用6位字元以下的密碼,其實不管那種系統低於6位口令非常容易破解.--//而且oracle預設還保證舊口令模式在sys.user$文件中,破解這個更容易.我僅僅寫一些例子:1.環境:SYS ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類