内容简介:安装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 主从数据库搭建》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:- 搭建Redis主从+哨兵模式
- Docker搭建MySQL主从复制
- MySQL主从复制搭建及详解
- redis的主从集群环境搭建
- MySQL -- 基于Docker搭建主从集群
- MySQL 主从复制搭建,基于日志(binlog)
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Where Wizards Stay Up Late
Katie Hafner / Simon & Schuster / 1998-1-21 / USD 16.00
Twenty five years ago, it didn't exist. Today, twenty million people worldwide are surfing the Net. "Where Wizards Stay Up Late" is the exciting story of the pioneers responsible for creating the most......一起来看看 《Where Wizards Stay Up Late》 这本书的介绍吧!