mysql数据库读写分离，mysql数据库读写
grant replication slave on *.* to 'rep'@'172.17.1.%' identified by '12QAZXCvfr34';
change master to master_host='172.17.1.150',master_user='rep',master_password='12QAZXCvfr34',master_log_file='mysql-bin.000001',master_log_pos=107;
master配置：
[mysql]
port = 3306
socket = /mysql/mysqld.sock
prompt="\u@\h \d>"
[mysqld]
#Instance
server-id = 44170
port = 3306
socket = /mysql/mysqld.sock
pid-file = /mysql/mysqld.pid
datadir = /mysql/data/
tmpdir = /tmp/
log-error = /mysql/mysqld.err
#Bin-log parameter
log-bin = /mysql/binlog/mysql-bin
log-bin-index = /mysql/binlog/mysql-bin.index
binlog_cache_size = 12m
binlog_format = mixed
sync_binlog = 0
max_binlog_cache_size = 1024m
max_binlog_size = 512m
expire_logs_days = 7
#Server parameter
skip_name_resolve
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES'
max_allowed_packet = 64m
max_connections = 8192
table_open_cache = 1024
open_files_limit = 8192
thread_cache_size = 512
query_cache_size = 0
tmp_table_size = 512m
max_heap_table_size = 2048m
#MyISAM parameter
skip-external-locking
join_buffer_size = 16m
sort_buffer_size = 16m
read_buffer_size = 64m
read_rnd_buffer_size = 64m
key_buffer_size = 16384m
#Innodb parameter
innodb_data_file_path = ibdata1:1024m:autoextend
innodb_file_per_table = 1
innodb_log_file_size = 64m
innodb_log_files_in_group = 2
innodb_log_buffer_size = 4m
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 256m
innodb_additional_mem_pool_size = 16m
innodb_io_capacity = 200
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 30
long_query_time = 3
slow_query_log = ON
#Slave GTID
gtid-mode = on
log-slave-updates = true
enforce-gtid-consistency = true
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
slave-parallel-workers = 2
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
[mysqldump]
quick
max_allowed_packet = 64m
socket=/mysql/mysqld.sock
[myisamchk]
key_buffer_size = 2048m
sort_buffer_size = 256m
read_buffer = 256m
write_buffer = 64m
[mysqlhotcopy]
socket = /mysql/mysqld.sock
[mysqladmin]
port = 3306
socket = /mysql/mysqld.sock
slave配置：
[mysql]
port = 3306
socket = /mysql/mysqld.sock
prompt="\u@\h \d>"
[mysqld]
#Instance
server-id = 44170
port = 3306
socket = /mysql/mysqld.sock
pid-file = /mysql/mysqld.pid
datadir = /mysql/data/
tmpdir = /tmp/
log-error = /mysql/mysqld.err
#Bin-log parameter
log-bin = /mysql/binlog/mysql-bin
log-bin-index = /mysql/binlog/mysql-bin.index
relay-log = /mysql/binlog/relay-log
relay-log-index = /mysql/binlog/relay-log.index
binlog_cache_size = 12m
binlog_format = mixed
sync_binlog = 0
max_binlog_cache_size = 1024m
max_binlog_size = 512m
expire_logs_days = 7
#Server parameter
skip_name_resolve
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES'
max_allowed_packet = 64m
max_connections = 8192
table_open_cache = 1024
open_files_limit = 8192
thread_cache_size = 512
query_cache_size = 0
tmp_table_size = 512m
max_heap_table_size = 2048m
#MyISAM parameter
skip-external-locking
join_buffer_size = 16m
sort_buffer_size = 16m
read_buffer_size = 64m
read_rnd_buffer_size = 64m
key_buffer_size = 16384m
#Innodb parameter
innodb_data_file_path = ibdata1:1024m:autoextend
innodb_file_per_table = 1
innodb_log_file_size = 64m
innodb_log_files_in_group = 2
innodb_log_buffer_size = 4m
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 256m
innodb_additional_mem_pool_size = 16m
innodb_io_capacity = 200
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 30
long_query_time = 3
slow_query_log = ON
#Slave GTID
gtid-mode = on
log-slave-updates = true
enforce-gtid-consistency = true
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
slave-parallel-workers = 2
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
[mysqldump]
quick
max_allowed_packet = 64m
socket=/mysql/mysqld.sock
[myisamchk]
key_buffer_size = 2048m
sort_buffer_size = 256m
read_buffer = 256m
write_buffer = 64m
[mysqlhotcopy]
socket = /mysql/mysqld.sock
[mysqladmin]
port = 3306
socket = /mysql/mysqld.sock
amoeba主要是针对数据切分方面、读写分离、负载均衡、故障恢复。首先说明一下amoeba 跟 mysql proxy在读写分离的使用上面的区别。在mysql proxy 上面如果想要读写分离并且 读集群、写集群 机器比较多情况下，用mysql proxy 需要相当大的工作量，目前mysql proxy没有现成的 lua脚本。mysql proxy根本没有配置文件，lua脚本就是它的全部，当然lua是相当方便的。那么同样这种东西需要编写大量的脚本才能完成一个复杂的配置。amoeba目标是走产品化这条路。只需要进行相关的配置就可以满足需求。一、Master/Slave 结构读写分离：Master： server1 （可读写）slaves：server2、server3、server4（3个平等的数据库。只读/负载均衡）amoeba提供读写分离pool相关配置。并且提供负载均衡配置。可配置server2、server3、server4形成一个虚拟的 virtualSlave,该配置提供负载均衡、failOver、故障恢复功能Xml代码<dbServer name="virtualSlave" virtual="true"><poolConfig><className>com.meidusa.amoeba.server.MultipleServerPool</className><!-- 负载均衡参数 1=ROUNDROBIN , 2=WEIGHTBASED --><property name="loadbalance">1</property><!-- 参与该pool负载均衡的poolName列表以逗号分割 --><property name="poolNames">server2,server3,server4</property></poolConfig></dbServer>如果不启用数据切分，那么只需要配置QueryRouter属性wirtePool=server1readPool=virtualSlave<queryRouter><className>com.meidusa.amoeba.mysql.parser.MysqlQueryRouter</className><property name="LRUMapSize">1500</property><property name="defaultPool">server1</property><property name="writePool">server1</property><property name="readPool">virtualSlave</property><property name="needParse">true</property>
mysql没有分离附加，推荐用dump命令
或者如果你的机子可以运行PHP程序的话，你可以安装一个phpmyadmin，用这个来备份你的数据库。
这样备份下来的文件实际上是一个像MS SQL里的.sql文件，里面全是SQL语句。
数据的保存是通过insert语句保存下来的。
这样，你就可以在其它机子上通过“执行”这个“.sql”来把你的数据导入到新的环境中了