In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
one。 Environment description
Operating system version: centos 7.2
Master server: 192.168.0.72 node2
Slave server: 192.168.0.73 node3
Scheduling server MySQL-Proxy:192.168.0.71 node1
two。 Configure mysql master to install mysql from 2.1
Both node2 and node3 need to be installed
[lirou@node2 ~] $sudo yum-y install mariadb-server
2.2 modify the configuration file
Master profile:
Skip_name_resolve
Innodb_file_per_table
Server_id = 1
Log_bin = / var/lib/mysql/bin_log
Slave profile:
Skip_name_resolve
Innodb_file_per_table
Server_id = 2
Log_bin = / var/lib/mysql/bin_log
Read_only
Note: the master server must turn on binary logging because master-slave replication depends on master. And the server_id of the master and slave servers must be different.
2.3 start the service
Both node2 and node3 need to start the service
[lirou@node3 ~] $sudo systemctl start mariadb
2.4 configure master and slave servers
On the Master server
Assign the account number of permission for slave hosts to access master:
[lirou@node2 ~] $mysql-uroot
MariaDB [(none)] > GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *. * TO 'repl_user'@'192.168.0.%' IDENTIFIED BY' repl_pass'
View the binary log location on master
MariaDB [(none)] > show master status
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |
+-+
| | binlog.000001 | 425 |
+-+
On the slave server
Mysql > CHANGE MASTER TO MASTER_HOST='192.168.0.72', MASTER_USER='repl_user', MASTER_PASSWORD='repl_pass', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=425
Mysql > slave start; # enable slave synchronization
Mysql > show slave status\ G; # check whether the synchronization is successful
three。 Configure mysql-proxy3.1 download installation package
~] # wget wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
~] # sudo tar xf https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz-C / usr/local
~] # mv / usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit / usr/local/mysql-proxy
3.2 configure mysql-proxy
Create a lua script directory
~] # cd / usr/local/mysql-proxy
Mysql-proxy] # mkdir lua
Create a log directory
Mysql-proxy] # mdkir logs
Copy read-write separation configuration files and administrative scripts to the lua script directory
Mysql-proxy] # cp share/doc/mysql-proxy/rw-splitting.lua. / lua
Mysql-proxy] # cp share/doc/mysqp-proxy/admin-sql.lua. / lua
Create a profile
~] # vim / etc/mysql-proxy.conf
[mysql-proxy]
User = root the user who runs the agent
Admin-username = mysql_proxy_user # mysql-proxy users who connect to the back-end mysql server
Admin-password = mysql_proxy_pass # password for mysql-proxy to connect to the backend mysql server
Proxy-address = 192.168.0.71 IP and port listened for by 3307 # mysql-proxy. Default is 4040.
Proxy-read-only-backend-addresses = 192.168.0.73 # read-only service
Proxy-backend-addresses = 192.168.0.72 # read-write server
Proxy-lua-script = / usr/local/mysql-proxy/lua/rw-splitting.lua # indicates read-write separation configuration file
Admin-lus-script = / usr/local/mysql-proxy/lua/admin-sql.lua # manage script path
Log-file = / usr/local/mysql-proxy/logs/mysql-proxy.log # Log file path
Log-level = debug # logging level
Daemon = whether the true # program is started on the backend
Keepalive = true # mysql-proxy after a crash, try to restart
Modify read-write configuration file
Vim / usr/local/mysql-proxy/lua/rw-splitting.lua
If not proxy.global.config.rwsplit then
Proxy.global.config.rwsplit = {
Min_idle_connections = 1, # when the number of connections exceeds 4 by default, read-write separation begins, instead of 1.
Max_idle_connections = 1, # default 8, changed to 1
Is_debug = false
}
Start mysql-proxy
~] # / usr/local/mysql-proxy/bin/mysql-proxy-- defaults-file=/etc/mysql-proxy.cnf
Users who create mysql-proxy connections on mysql
Mysql > GRANT ALL ON *. * TO 'mysql_proxy_user'@'192.168.0.%' IDENTIFIED BY' mysql_proxy_pass'
four。 Test read-write separation
Test writing
Mysql-umysql_proxy_user-pmysql_proxy_pass-h292.168.0.71-- port=3307
Mysql > create databases master
Mysql > create table test (id int)
Mysql > insert into master.test value (1)
View the slave server:
You can see the synchronization from the server.
Test reading
Number of query statements on the main server
Query the number of statements from server
Connect mysql-proxy to execute a read statement
Number of query statements on the main server
Query the number of statements from server
We can see that the query statement is executed on the slave server.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.