MySQL 5.7 主从数据库搭建

栏目: 数据库 · Mysql · 发布时间: 6年前

内容简介:安装MySQL5.7,主数据库为192.168.2.221,从数据库为192.168.2.222,服务器内存8G批量插入语句n为300时,每秒插入达到35000条数据

1.数据库安装

安装 MySQL 5.7,主数据库为192.168.2.221,从数据库为192.168.2.222,服务器内存8G

sudo apt-get install mysql-server

2.主数据库配置

  • 修改/etc/mysql/my.cnf

    #master
    [mysqld]
    #common  port = 3306  bind-address = 0.0.0.0  skip-name-resolve  max_connections = 512
    #replicate  server_id = 1  #replicate database  binlog-do-db = test  log_bin = mysql-bin
    #innodb  innodb_buffer_pool_size = 6G  innodb_flush_method = O_DIRECT  innodb_log_buffer_size = 16M  innodb_log_file_size = 2G  innodb_log_files_in_group = 2
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    
  • 进入数据库,添加同步用户

    shell>mysql -uroot -p
    mysql>create user ‘repl’@’%’ identified by ‘password’;  mysql>grant replication slave on * . * to ‘repl’@’%’;
    
  • 记录master信息,File和Position

    mysql>show master status\G

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

    File: mysql-bin.000001

    Position: 154

    Binlog_Do_DB: test

    Binlog_Ignore_DB:

    Executed_Gtid_Set:

  • 重启数据库

    sudo /etc/init.d/mysql restart
    

3.从数据库配置

  • 修改/etc/mysql/my.cnf

    #slave
    [mysqld]
    #common  port = 3306  bind-address = 0.0.0.0  skip-name-resolve  max_connections = 512
    #replicate  server_id = 2  #replicate database  replicate-do-db = test
    #innodb  innodb_buffer_pool_size = 6G  innodb_flush_method = O_DIRECT  innodb_log_buffer_size = 16M  innodb_log_file_size = 2G  innodb_log_files_in_group = 2  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    
  • 配置slave

    shell>mysql -uroot -p  mysql>change master to master_host =’192.168.2.221’,master_port=3306,master_user=’repl’,master_password=’password’,master_log_file=’mysql-bin.000001’,master_log_pos=154;  mysql>start slave;
    
  • 重启数据库

    shell>sudo /etc/init.d/mysql restart

    shell>mysql -uroot -p

    mysql>show slave status\G;

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

    Slave_IO_State: Waiting for master to send event

    Master_Host: 192.168.2.221

    Master_User: repl

    Master_Port: 3306

    Connect_Retry: 60

    Master_Log_File: mysql-bin.000006

    Read_Master_Log_Pos: 84848971

    Relay_Log_File: database-2-relay-bin.000018

    Relay_Log_Pos: 84849184

    Relay_Master_Log_File: mysql-bin.000006

    Slave_IO_Running: Yes

    Slave_SQL_Running: Yes

    Replicate_Do_DB: test

    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: 84848971

    Relay_Log_Space: 84849443

    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: 1

    Master_UUID: afa582dc-2628-11e6-9226-408d5ce1124f

    Master_Info_File: /var/lib/mysql/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:

    Executed_Gtid_Set:

    Auto_Position: 0

    Replicate_Rewrite_DB:

    Channel_Name:

    Master_TLS_Version:

  • 连接成功

4.批量插入性能

批量插入语句

insert into mt_data(mac,time,device_id,intensity,ap_mac,channel,virtual_type)values(……)*n

n为300时,每秒插入达到35000条数据

Linux公社的RSS地址: https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址: https://www.linuxidc.com/Linux/2018-08/153815.htm


以上所述就是小编给大家介绍的《MySQL 5.7 主从数据库搭建》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Smarter Than You Think

Smarter Than You Think

Clive Thompson / Penguin Press HC, The / 2013-9-12 / USD 27.95

It's undeniable—technology is changing the way we think. But is it for the better? Amid a chorus of doomsayers, Clive Thompson delivers a resounding "yes." The Internet age has produced a radical new ......一起来看看 《Smarter Than You Think》 这本书的介绍吧!

Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

RGB HSV 转换
RGB HSV 转换

RGB HSV 互转工具

RGB CMYK 转换工具
RGB CMYK 转换工具

RGB CMYK 互转工具