你就不要折腾这个了 直接使用集群
我直接给你资料好了 以前给客户部署的 任意节点都可以读写
有些可能有些过时了
参考资料
http://matthewcasperson.blogspot.jp/2013/07/setting-up-galera-cluster-in-centos-6.htmlhttp://blog.laimbock.com/2014/07/08/howto-setup-mariadb-galera-cluster-10-on-centos/comment-page-1/https://mariadb.com/kb/en/mariadb/documentation/replication/galera/mariadb-galera-cluster-known-limitations/https://mariadb.com/kb/en/mariadb/documentation/replication/galera/galera-cluster-system-variables/#wsrep_replicate_myisamhttp://tecadmin.net/setup-mariadb-galera-cluster-5-5-in-centos-rhel/echo "HOSTNAME=dbnode1" >>/etc/sysconfig/network
hostname "dbnode1"
echo "HOSTNAME=dbnode2" >>/etc/sysconfig/network
hostname "dbnode2"
echo "NETWORKING_IPV6=yes">>/etc/sysconfig/network
echo "nameserver 74.207.241.5
nameserver 74.207.242.5
nameserver 2600:3c01::2
nameserver 2600:3c01::3
options rotate">/etc/resolv.conf
echo -n "
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
2600:3c01::f03c:91ff:fedf:9b24/64 db1
">/etc/hosts
#IP 分配
DB Node 1 173.255.199.172 / 192.168.183.172
DB Node 2 198.58.114.176 / 192.168.185.74
echo "[mariadb]
name = MariaDB
baseurl =
http://yum.mariadb.org/5.5/centos6-amd64gpgkey=
https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1">/etc/yum.repos.d/MariaDB.repo
yum -y install epel-release
yum -y install socat
yum erase *mysql*
yum -y install MariaDB-Galera-server MariaDB-client galera
/etc/init.d/mysql start
mysql_secure_installation
#mysql_upgrade
mysql -u root -pAa1111111111111
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Aa11111111' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit
service mysql stop
#### Testing
mysql -u root -pA111111111
# Check Status
show status like 'wsrep%';
#DB1
CREATE DATABASE clustertest;
CREATE TABLE clustertest.mycluster ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), ipaddress VARCHAR(20), PRIMARY KEY(id));
INSERT INTO clustertest.mycluster (name, ipaddress) VALUES ("db1", "10.0.0.9");
SELECT * FROM clustertest.mycluster;
#DB2
SELECT * FROM clustertest.mycluster;
INSERT INTO clustertest.mycluster (name, ipaddress) VALUES ("db2", "10.0.0.10");
show databases;
MYSQL 数据库 DMM 架构设计
使用 MariaDB 作为数据库引擎 表使用 InnoDB storage engine.
可能有部分功能性缺陷 可以参考
https://mariadb.com/kb/en/mariadb/documentation/replication/galera/mariadb-galera-cluster-known-limitations/因为按你们的需求 只能使用多主的架构
这样可以降低数据库并发的压力 提高写入效率
如果性能下降 增加数据库节点即可
所有数据全部都会自动同步 不需要人工干预
使用方式: 当前 2 节点可用
准备一个域名
将域名 DNS 改为
www.dnspod.com 国际站 非国内站
增加解析记录 (假如
test.com 是你的域名)
CNAME 记录 一条
sqlcloud.test.com CNAME 记录
node.test.comA 记录 (IPv4 协议)
node A 记录 default 173.255.199.172
node A 记录 default 198.58.114.176
AAAA 记录 (IPv6 协议)
node AAAA default 2600:3c00::f03c:91ff:fe89:e46
node AAAA default 2600:3c00::f03c:91ff:fe89:ea9
TTL 设置为 300 或 600
default 是 GEO 分流 可以设置一个大洲或者一个国家每服务器
使用 DNSPOD 国际版 是因为这个支持多记录轮询模式
例如如果有 1000 个 DNS 请求 DNSPOD 会使用 RR 模式 轮流返回可用 IP
就是每个 IP 分配 500 个请求,这样实现多数据库服务器写入 提高性能
然后链接语句
使用
sqlcloud.test.com:3306 会随机分配 IP
root
1111111111
数据库名称
读写数据 都会自动同步在多个节点中
这个平台可测试可以商用
插入 10 万条数据 无脑裂问题
如果有出现 可以增加仲载节点解决
PS. 已修正 MYSQL 用户表,MYISAM 无法自动同步问题
C7 文档 2
systemctl status firewalld
hostnamectl set-hostname cmsqlnode2 --static
wsrep_provider=/usr/lib/galera/
libgalera_smm.sowsrep_cluster_address=gcomm://其他集群 IP
wsrep_cluster_name='cluster1'
wsrep_node_address='本机 IP'
wsrep_node_name='db1'
ip addr show
vi /etc/my.cnf.d/server.cnf
cat /etc/my.cnf.d/server.cnf
node1
Server Login:
IP Address: 1111111111
Username: root
Password: 11111111111111
node2
IP Address: 11111111111
Username: root
Password: 111111111111
cat >>/etc/sysconfig/network-scripts/ifcfg-eth1 <<EOF
IPADDR=10.40.211.2
NETMASK=255.255.255.0
EOF
service network restart
yum erase *mysql*
yum -y install epel-release
yum -y install socat
echo "[mariadb]
name = MariaDB
baseurl =
http://yum.mariadb.org/10.0/centos7-amd64gpgkey=
https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1">/etc/yum.repos.d/MariaDB.repo
echo "[mariadb]
name = MariaDB
baseurl =
http://yum.mariadb.org/5.5/centos6-amd64gpgkey=
https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1">/etc/yum.repos.d/MariaDB.repo
yum -y install net-tools MariaDB-Galera-server MariaDB-client galera
/etc/init.d/mysql start
/etc/init.d/mysql restart
mysql_secure_installation
#mysql_upgrade
mysql -u root -pAa111111111111111
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Aa11111111111111' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit
service mysql stop
#### Testing
mysql -u root -pAa111111111111
#### Check Status
show status like 'wsrep%';
######节点测试
#DB1
CREATE DATABASE clustertest;
CREATE TABLE clustertest.mycluster ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), ipaddress VARCHAR(20), PRIMARY KEY(id));
INSERT INTO clustertest.mycluster (name, ipaddress) VALUES ("db1", "10.0.0.9");
SELECT * FROM clustertest.mycluster;
#DB2
SELECT * FROM clustertest.mycluster;
INSERT INTO clustertest.mycluster (name, ipaddress) VALUES ("db2", "10.0.0.10");
show databases;
配置文件 多个
#IP 分配
DB Node 1 173.255.199.172 / 192.168.183.172
DB Node 2 198.58.114.176 / 192.168.185.74
DB1
============================
vi /etc/my.cnf.d/server.cnf
[mysqld]
wsrep_replicate_myisam=1
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/
libgalera_smm.sowsrep_cluster_address=gcomm://192.168.185.74
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.183.172'
wsrep_node_name='db1'
wsrep_sst_method=rsync
wsrep_sst_auth=root:Aa258369
innodb_doublewrite=1
innodb_flush_log_at_trx_commit=2
/etc/init.d/mysql bootstrap
DB2
============================
vi /etc/my.cnf.d/server.cnf
wsrep_cluster_address=gcomm://192.168.183.172
wsrep_provider=/usr/lib64/galera/
libgalera_smm.sowsrep_replicate_myisam=1
wsrep_sst_method=rsync
wsrep_sst_auth=root:Aa258369
wsrep_cluster_name='cluster1'
wsrep_node_address='192.168.185.74'
wsrep_node_name='db2'
wsrep_slave_threads=4
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
innodb_flush_log_at_trx_commit=2
/etc/init.d/mysql bootstrap
/etc/init.d/mysql restart
主
cat >/etc/my.cnf.d/server.cnf <<EOF
[mysqld]
binlog_format=ROW
default_storage_engine=innodb
wsrep_replicate_myisam=1
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
innodb_flush_log_at_trx_commit=2
query_cache_size=0
query_cache_type=0
#bind-address=0.0.0.0
[galera]
## WSREP options
wsrep_provider=/usr/lib64/galera/
libgalera_smm.so#wsrep_provider_options=
wsrep_cluster_name='cluster1'
wsrep_cluster_address=gcomm://10.40.211.2 ##主节点 IP
wsrep_node_name='db1'
wsrep_node_address='10.40.211.2'
#wsrep_node_incoming_address=
wsrep_slave_threads=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_drupal_282555_workaround=0
wsrep_causal_reads=0
wsrep_sst_method=rsync
wsrep_sst_auth=root:Aa258369
EOF
从
cat >/etc/my.cnf.d/server.cnf <<EOF
[mysqld]
binlog_format=ROW
default_storage_engine=innodb
wsrep_replicate_myisam=1
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
innodb_flush_log_at_trx_commit=2
query_cache_size=0
query_cache_type=0
#bind-address=0.0.0.0
[galera]
## WSREP options
wsrep_provider=/usr/lib64/galera/
libgalera_smm.so#wsrep_provider_options=
wsrep_cluster_name='cluster1'
wsrep_cluster_address=gcomm://10.40.211.2 ##主节点 IP
wsrep_node_name='db2'
wsrep_node_address='10.40.211.1'
#wsrep_node_incoming_address=
wsrep_slave_threads=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_drupal_282555_workaround=0
wsrep_causal_reads=0
wsrep_sst_method=rsync
wsrep_sst_auth=root:Aa258369
EOF
Test
SELECT table_schema "DB Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;
SELECT table_name AS "Tables",
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = "$DB_NAME"
ORDER BY (data_length + index_length) DESC;
SELECT TABLE_NAME AS "Table Name",
table_rows AS "Quant of Rows", ROUND( (
data_length + index_length
) /1024, 2 ) AS "Total Size Kb"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'YOUR SCHEMA NAME/DATABASE NAME HERE'
LIMIT 0 , 30
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;
MYSQL
建表
create database amarokdb;
grant usage on *.* to amarokuser@localhost identified by 'amarokpasswd';
grant all privileges on amarokdb.* to amarokuser@localhost ;
建立用户 分配权限
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
删除用户
DROP USER ‘ demo ’@‘ localhost ’;