• www.tbplay918.com_www.tbplay918.com【支付宝快捷支付】

  • 发布时间:2016-01-13 00:47 | 作者:yc | 来源:互联网 | 浏览:1200 次
  • www.tbplay918.com_www.tbplay918.com【支付宝快捷支付】要手动发动slave

    [root@localhost etc]# service mysqld restart

    [root@localhost ~]# mysql -p

    Enter password:

    mysql> start slave;

    Query OK, 0 rows affected (0.05 sec)

    mysql> create table t2 like t1;| 或许mysql> truncate t2;清空主动增添

    Query OK, 0 rows affected (0.02 sec)

    mysql> select * from t2;

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

    | id | name|

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

    |1 | user1 |

    |3 | user2 |

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

    2 rows in set (0.01 sec)

    【最大略的双主模型 等于这么!】

    引荐:

    多主,且高可用的办理规划:

    MMM:Multi Master MySQL

    MHA:MySQL HA

    根据ssl的仿制【大年夜约的讲一下】

    mysql> show variables like "%ssl%";

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

    | Variable_name | Value |

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

    | have_openssl| NO|

    | have_ssl| NO|

    | ssl_ca||

    | ssl_capath||

    | ssl_cert||

    | ssl_cipher||

    | ssl_key||

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

    7 rows in set (0.00 sec)

    需求编译装配好后,才气enable ssl的功用

    client发送证书给server

    server验证客户端

    运用措施

    mysql> help change master to

    Syntax:

    CHANGE MASTER TO option [, option] ...

    option:

    MASTER_BIND = 'interface_name'

    | MASTER_HOST = 'host_name'

    | MASTER_USER = 'user_name'

    | MASTER_PASSWORD = 'password'

    | MASTER_PORT = port_num

    | MASTER_CONNECT_RETRY = interval

    | MASTER_HEARTBEAT_PERIOD = interval

    | MASTER_LOG_FILE = 'master_log_name'

    | MASTER_LOG_POS = master_log_pos

    | RELAY_LOG_FILE = 'relay_log_name'

    | RELAY_LOG_POS = relay_log_pos

    | MASTER_SSL = {0|1}

    | MASTER_SSL_CA = 'ca_file_name'

    | MASTER_SSL_CAPATH = 'ca_directory_name'

    | MASTER_SSL_CERT = 'cert_file_name'

    | MASTER_SSL_KEY = 'key_file_name'

    | MASTER_SSL_CIPHER = 'cipher_list'

    | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}

    | IGNORE_SERVER_IDS = (server_id_list)

    server_id_list:

    [server_id [, server_id] ... ]

    仿制有关的文件:

    master.info: 文这篇文章件,保留从效劳器毗连至主效劳时所需求的信息,每行一个值;

    有了这些才会主动发动change master to,删除了就不会主动毗连了

    relay-log.info: 文这篇文章件,保留了仿制方位:包孕二进制日志和中继日志的文件及方位;

    [root@localhost data]# cat /mydata/data/master.info

    18

    master-log.

    107

    192.168.81.133

    repluser

    replpass

    3306

    60

    0

    0

    1800.000

    0

    000

    0

    [root@localhost data]# cat /mydata/data/relay-log.info

    /mydata/relaylogs/relay-bin.

    254

    master-log.

    107

    370

    [Action !]

    这两个文件是实时更新的,要是碰到这种状况,maser效劳器非分特别忙碌,

    那么这两个文件会被几回再三的修正,那么I/0的功用必然会低落

    mysql> show global variables like "sync%";

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

    | Variable_name| Value |

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

    | sync_binlog| 0|

    | sync_frm| ON|

    | sync_master_info| 0|

    | sync_relay_log| 0|

    | sync_relay_log_info | 0|

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

    5 rows in set (0.05 sec)

    为了仿制的安然性:

    sync_master_info = 1

    sync_relay_log = 1

    sync_relay_log_info = 1

    装配percona-toolkit

    [root@localhost home]# yum install ./percona-toolkit-2.2.4-1.noarch.rpm

    运用措施

    [root@localhost home]# pt-heartbeat -h

    [root@localhost home]# pt器械这么多

    pt-agentpt-fingerprintpt-pmppt-table-checksum

    pt-alignpt-fk-error-loggerpt-query-digestpt-table-sync

    pt-archiverpt-heartbeatpt-show-grantspt-table-usage

    pt-config-diffpt-index-usagept-siftpt-upgrade

    pt-deadlock-loggerpt-ioprofilept-slave-delaypt-variable-advisor

    pt-diskstatspt-killpt-slave-findpt-visual-explain

    pt-duplicate-key-checkerpt-mextpt-slave-restartptx

    pt-fifo-splitpt-mysql-summarypt-stalk

    pt-findpt-online-schema-changept-summary

    反省设置设备摆设信息

    [root@localhost home]# pt-summary

    # Percona Toolkit System Summary Report ######################

    Date | 2015-10-17 09:25:30 UTC (local TZ: CST +0800)

    Hostname | localhost.localdomain

    Uptime | 11:59,3 users,load average: 0.00, 0.00, 0.00

    Platform | Linux

    Release | CentOS release 6.7 (Final)

    Kernel | 2.6.32-573.el6.x86_64

    Architecture | CPU = 64-bit, OS = 64-bit

    Threading | NPTL 2.12

    SELinux | Enforcing

    Virtualized | VMWare

    # Processor ##################################################

    Processors | physical = 2, cores = 4, virtual = 4, hyperthreading = no

    Speeds | 4x2195.089

    Models | 4xIntel(R) Core(TM) i3-2328M CPU @ 2.20GHz

    Caches | 4x3072 KB

    # Memory #####################################################

    Total | 2.0G

    Free | 1.5G

    Used | physical = 487.5M, swap allocated = 2.1G, swap used = 0.0, virtual = 487.5M

    Buffers | 50.1M

    Caches | 181.6M

    Dirty | 12 kB

    UsedRSS | 157.0M

    Swappiness | 60

    DirtyPolicy | 20, 10

    DirtyStatus | 0, 0

    # Mounted Filesystems ########################################

    FilesystemSize Used TypeOptsMountpoint

    /dev/mapper/VolGroup-lv_home37G1% ext4rw/home

    /dev/mapper/VolGroup-lv_root50G5% ext4rw/

    /dev/sda1477M9% ext4rw/boot

    tmpfs1002M0% tmpfs rw,rootcontext="system_u:object_r:tmpfs_t:s0" /dev/shm

    # Disk Schedulers And Queue Size #############################

    dm-0 | 128

    dm-1 | 128

    dm-2 | 128

    sda | [cfq] 128

    sr0 | [cfq] 128

    # Disk Partioning ############################################

    DeviceTypeStartEndSize

    ============ ==== ========== ========== ==================

    /dev/dm-0Disk0

    /dev/dm-1Disk

    /dev/dm-2Disk2

    /dev/sdaDisk0

    /dev/sda1Part40

    /dev/sda2Part

    /dev/sr0Disk

    /dev/sr0p1Part

    # Kernel Inode State #########################################

    dentry-state | 45417 37192 45 0 0 0

    file-nr | 768 0

    inode-nr | 41113 103

    # LVM Volumes ################################################

    LVVGAttrLSizePool Origin Data%Meta%Move Log Cpy%Sync Convert

    lv_home VolGroup -wi-ao---- 37.41g

    lv_root VolGroup -wi-ao---- 50.00g

    lv_swap VolGroup -wi-ao----2.09g

    # LVM Volume Groups ##########################################

    VGVSizeVFree

    VolGroup 89.51g0

    # RAID Controller ############################################

    Controller | No RAID controller detected

    www.tbplay918.com_www.tbplay918.com【支付宝快捷支付】

    # Network Config #############################################

    FIN Timewww78comout | 60

    Port Range | 61000

    # Interface Statistics #######################################

    interfacerx_bytes rx_packetsrx_errorstx_bytes tx_packetstx_errors

    ========= ========= ========== ========== ========== ========== ==========

    lo00

    eth

    # Network Devices ############################################

    DeviceSpeedDuplex

    ========= ========= =========

    eth01000Mb/sFull

    # Network Connections ########################################

    Connections from remote IP addresses

    192.168.81.13

    192.168.81.1331

    Connections to local IP addresses

    192.168.81.1324

    Connections to top 10 local ports

    223

    States of connections

    ESTABLISHED4

    LISTEN5

    # Top Processes ##############################################

    PID USERPRNIVIRTRESSHR S %CPU %MEMTIME+COMMAND

    1 root200 19232 1520 1228 S0.00.10:03.21 init

    2 root S0.00.00:00.04 kthreadd

    3 rootRT0000 S0.00.00:00.61 migration/0

    4 root S0.00.00:00.30 ksoftirqd/0

    5 rootRT0000 S0.00.00:00.00 stopper/0

    6 rootRT0000 S0.00.00:00.19 watchdog/0

    7 rootRT0000 S0.00.00:00.80 migration/1

    8 rootRT0000 S0.00.00:00.00 stopper/1

    9 root S0.00.00:00.12 ksoftirqd/1

    # Notable Processes ##########################################

    PIDOOMCOMMAND

    11078-17sshd

    # Simplified and fuzzy rounded vmstat (wait please) ##########

    procs---swap-- -----io---- ---system---- --------cpu--------

    rbsisobiboircsussyilwast

    10000

    0

    0 10000

    4500 10000

    0 10000

    # The End ####################################################

    从效劳器意外溃散时,主张运用pt-slave-start指令来发动slave;

    根据行和根据句子仿制:

    根据句子:

    数据量小;易于反省;适应性强;

    有些句子无法做准确仿制;无法对运用了触发器、存储进程等代码的运用完结准确仿制;

    根据行:

    能够准确完结有着触发器、存储进程等代码场景中的仿制;能完结的确统统的仿制功用;较少的CPU占用率;

    无法判别履行了什么样的SQL句子;数据量大大要大年夜;

    从效劳器后进于主效劳器:

    Seconds_Behind_Master: 0

    反省后进时长,要是后进,请不雅测此值

    mysql> show slave status\G

    Seconds_Behind_Master: 0

    要是数据不合营,办理法子

    1、从头备份并在从效劳器导入数据;

    2、pt-table-sync

    为了进步仿制时的数据安然性,在主效劳器上的设定:

    sync_binlog = 1

    innodb_flush_log_at_trx_commit = 1

    此参数的值设定为1,功用低落会较严酷;因而,平日设定为2等,

    此刻,主效劳器溃散仍旧有大概致使从效劳器无法获取到悉数的二进制日志工作;

    要是master意外溃散致使二进制日志中的某工作毁坏,能够在从效劳器运用如下参数纰漏:

    sql_slave_skip_counter = 0

    第三方仿制办理规划:Tungsten, Galera

    仿制要思虑的事儿:

    作甚雪崩效应?

    怎么缓存,高效掷中缓存?[轮巡,对Sql句子hash取模?]

    从效劳器写入的压力并不比主效劳器的压力小,

    营业的手动主动提交,几回再三同步到磁盘、

    二进制日志文件的几回再三写入读出,中继日志的写入读出,

    SQl I/OSlave 单线程忙碌

    从效劳器后进于主效劳器的必定,

    中途断电,缓存中的二进制文件未写入到磁盘,怎么办?

    远程跨地,怎么同步数据?

    有了这位么多的从效劳器,数据还需求备份吗?

    2015.10.18

    这篇文章出自 “生命不息,折腾不止

    【减轻仿制压力】仿制过滤器,指定需求仿制的白名单,或许需求纰漏的黑名单

    [root@localhost ~]# cd /etc/

    [root@localhost etc]# cp my.cnf{,.master}

    [root@localhost etc]# ll my.cnf*

    -rw-r--r--. 1 root root 4686 10月 13 04:43 my.cnf

    -rw-r--r--. 1 root root 4686 10月 14 20:00 my.cnf.master

    vim my.cnf:

    [mysqld]

    master:

    binlog_do_db=

    binlog_ignore_db=

    slave:

    replicate_do_db=

    replicate_ignore_db=

    www.tbplay918.com_www.tbplay918.com【支付宝快捷支付】

    replicate_do_table= db_name.table_name

    replicate_ignore_table=

    replicate_wild_do_table=

    replicate_wild_ignore_table=

    6、双主模型

    1)、在两台效劳器上各自树立一个具有仿制权限的用户;

    2)、修正设置设备摆设文件:

    【包管两边效劳器数据合营,才气这么做!,本演示是在曾经的一主一从的拓扑演化而来】

    #【主效劳器A上】

    [root@localhost etc]# cp /etc/my.cnf /etc/my.cnf.master

    [root@localhost etc]# vim /etc/my.cnf +61

    [mysqld]

    datadir= /mydata/data

    server-id = 1

    log-bin=/mydata/binlogs/master-bin

    relay_log = /mydata/relaylogs/relay-bin

    auto-increment-offset = 1#把稳肇端值序次不要写反

    auto-increment-increment = 2 #把稳步长序次需求写反

    skip_slave_start超出主动发动slave,因为主动的大略犯错

    [root@localhost etc]# mkdir -pv /mydata/relaylogs/

    mkdir: 已创立目录 "/mydata/relaylogs/"

    [root@localhost etc]# chown -R mysql:mysql /mydata/relaylogs/

    [root@localhost etc]# service mysqld restart

    [root@localhost ~]# mysql -p

    Enter password:

    mysql> show master status;

    保留此刻的状况,不要再操作这个mysql效劳器

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

    | File| Position |

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

    | master-bin. |107 |

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

    1 row in set (0.00 sec)

    # 【主效劳器B上】

    [root@localhost ~]# cp /etc/my.cnf /etc/my.cnf.slave

    [mysqld]

    server-id = 11

    log-bin=/mydata/binlogs/master-log

    binlog_format=mixed #启用

    # read-only = ON # 注释原先的只读

    auto-increment-offset = 2#把稳肇端值序次不要写反

    auto-increment-increment = 2#把稳步长序次需求写反

    skip_slave_start #超出主动发动slave,因为主动的大略犯错

    [root@localhost ~]# mkdir -pv /mydata/binlogs/

    mkdir: 已创立目录 "/mydata/binlogs/"

    [root@localhost ~]# chown -R mysql:mysql /mydata/binlogs/

    [root@localhost etc]# service mysqld restart

    [root@localhost ~]# mysql -p

    Enter password:

    #为对方创立一个具有统统权限的用户

    mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';

    Query OK, 0 rows affected (0.00 sec)

    mysql> FLUSH PRIVILEGES;

    Query OK, 0 rows affected (0.01 sec)

    #反省SLAVE的状况

    mysql> SHOW SLAVE STATUS\G

    奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫 1. row 奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫

    Slave_IO_State:

    Master_Host: 192.168.81.132

    Master_User: repluser

    Master_Port: 3306

    Connect_Retry: 60

    Master_Log_File: master-bin.

    Read_Master_Log_Pos: 107

    Relay_Log_File: relay-bin.

    Relay_Log_Pos: 254

    Relay_Master_Log_File: master-bin.

    Slave_IO_Running: No没发动

    Slave_SQL_Running: No没发动

    用对方效劳器192.168.81.132的repluser账户,指向对方效劳器的日志文件

    mysql>CHANGE MASTER TOMASTER_HOST='192.168.81.132',MASTER_USER='repluser', MASTER_PASSWORD='replpass',MASTER_LOG_FILE = 'master-bin.',MASTER_LOG_POS = 107;

    Query OK, 0 rows affected (0.05 sec)

    mysql> start slave;

    Query OK, 0 rows affected (0.02 sec)

    mysql> show slave status\G

    奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫 1. row 奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫

    Slave_IO_State: Waiting for master to send event

    Maswww.77ter_Host: 192.168.81.132

    Master_User: repluser

    Master_Port: 3306

    Connect_Retry: 60

    Master_Log_File: master-bin.

    Read_Master_Log_Pos: 107

    Relay_Log_File: relay-bin.

    Relay_Log_Pos: 254

    Relay_Master_Log_File: master-bin.

    Slave_IO_Running: Yes

    Slave_SQL_Running: Yes

    反省主效劳器B的状况【把稳,不要再把效劳器B当slave的思惟】

    mysql> show master status;

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

    | File| Position |

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

    | master-log. |370 |

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

    1 row in set (0.00 sec)

    【在效劳器A操作,把主指向效劳器B】把稳你的iptables

    把稳:用对方效劳器192.168.81.133的repluser账户,指向对方效劳器的日志文件

    mysql> CHANGE MASTER TOMASTER_HOST='192.168.81.133',MASTER_USER='repluser', MASTER_PASSWORD='replpass',MASTER_LOG_FILE = 'master-log.',MASTER_LOG_POS = 370;

    Query OK, 0 rows affected (0.03 sec)

    mysql> show slave status\G

    奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫 1. row 奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫

    Slave_IO_State:

    Master_Host: 192.168.81.133

    Master_User: repluser

    Master_Port: 3306

    Connect_Retry: 60

    Master_Log_File: master-master-log.

    Read_Master_Log_Pos: 370

    Relay_Log_File: relay-bin.

    Relay_Log_Pos: 4

    Relay_Master_Log_File: master-master-log.

    Slave_IO_Running: No

    Slave_SQL_Running: No

    发动互为主从

    mysql> start slave ;

    Query OK, 0 rows affected (0.01 sec)

    mysql> show slave status\G

    奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫 1. row 奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫奸淫

    Slave_IO_State: Waiting for master to send event

    Master_Host: 192.168.81.133

    Master_User: repluser

    Master_Port: 3306

    Connect_Retry: 60

    Master_Log_File: master-log.

    Read_Master_Log_Pos: 370

    Relay_Log_File: relay-bin.

    Relay_Log_Pos: 254

    Relay_Master_Log_File: master-log.

    Slave_IO_Running: Yes

    Slave_SQL_Running: Yes

    【测验】在效劳器A上创立一个数据库,并运用这个库创立主动增添的表

    mysql> create database newdb;

    Query OK, 1 row affected (0.00 sec)

    【在效劳器B上】反省是不是存在

    mysql> show databases;

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

    | Database|

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

    | information_schema |

    | haha|

    | hellodb|

    | mysql|

    | newdb|

    | performance_schema |

    | test|

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

    7 rows in set (0.00 sec)

    mysql> use newdb;

    Database changed

    mysql> create table t1(id int unsigned not null primary key auto_increment,name char(20));

    Query OK, 0 rows affected (0.05 sec)

    mysql> insert into t1 (name) values ('tom'),('jerry');

    Query OK, 2 rows affected (0.01 sec)

    Records: 2Duplicates: 0Warnings: 0

    mysql> select * from t1;

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

    | id | name|

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

    |2 | tom|

    |4 | jerry |

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

    2 rows in set (0.00 sec)

    【测验】在效劳器A器上操作

    mysql> use newdb;

    Database changed

    mysql> insert into t1 (name) values ('user1'),('user2');

    Query OK, 2 rows affected (0.02 sec)

    Records: 2Duplicates: 0Warnings: 0

    mysql> select * from t1;

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

    | id | name|

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

    |2 | tom|

    |4 | jerry |

    |5 | user1 |

    |7 | user2 |

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

    4 rows in set (0.00 sec)

    mysql> select last_insert_id();

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

    | last_insert_id() |

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

    |5 |

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

    1 row in set (0.00 sec)

    【把稳】要是再次修正过mysqld设置设备摆设文件” 博客,谢绝转发!

  • 相关内容

友情链接: