MySQL Database (vi)

#####################################

MySQL Database master-Slave synchronization

Master-Slave synchronization: Let other database servers automatically synchronize data on the database server that is serving the service. Building Master-Slave synchronization (a) Ensure that the primary, data from the database is the same from the library must have data on the primary library (b) Configure the primary server to enable Binlog logging, server_id, authorized User (c) to configure the From server settings server_id[master]/etc/my.cnf[ mysqld]server_id=11log-bin=master11binlog-format= "mixed"/specify log format #systemctl start mysqld#mysql>grant replication   Slave on * * [email protected] "%" identified by "123456";  /authorized Slaveuser user to log in on all hosts, with permissions of replication Slaveshow master status; /view Binlog Log [slave]/etc/my.cnf[mysqld]server_id=12/Can not enable Binlog log Mysql>change Master to>master_host= "       192.168.4.11 ",/indicates the primary server address >master_user=" Slaveuser ",/indicates the use of Slaveuser authorized user to log on to the master server >mster_password=" 123456 ",            /authorized user's password >master_log_file= "master11.000001",/indicates that the primary server is now Binlog log >master_log_pos=154;    /primary server binlog log offset #start slave;   /Start replication #show slave status; /view slave status Slave_io_running:yes/io thread is running Slave_sql_running:yes/sql thread has run IO thread: Copy the SQL from the Master host Binlog log file into the native Relay-log file SQL thread: Perform the SQL statement in the native Relay-log file, reproduce the problem with the master data operation: IO thread status is no---> No connection to the primary server. SQL thread status is no---&Gt Master/Slave data #ls/var/lib/mysqlmaster.info/connection main server information relay-log.info/relay log message host name-relay-bin.xxxxxx/trunk log hostname-relay-bin.index Relay Log index file master-slave synchronization mode one main slave from one master from main---(Master---from)/1 is the Master of 2, 2 is 1 from and 3 is the main, 3 is 2 from main main structure (mutual master from) main Library configuration file (valid for all slave servers) only allow synchronization of libraries binlog_do_db= Library 1, library 2 does not allow the synchronization of the library binlog_ignore_db= Library 1, library 2 from the library configuration file only allow the synchronization of Libraries replicate_do_db= Library 1, library 2 does not allow synchronized Libraries replicate_ignore_db= Library 1, library 2-level replication Log_ slave_updates#########################################

Read and write separation of MySQL database

为了缓解主服务器的压力，采用读写分离，即客户端进行写操作时，进入主服务器，客户端进行读操作时，进入从服务器实现方式: maxscale软件 +  一主一从[master11][slave12]slave12是master11的从服务器[monitor13]#rpm -ivh maxscale..修改配置文件/etc/maxscale.cnf(a)指定数据库服务器[server1][server2](b)指定监控的数据库服务器(c)指定读写在那些服务器之间执行(d)定义管理服务(e)读写分离使用的端口 port=4006(f)管理服务使用的端口 port=4009(g)根据配置文件，在数据库服务器上添加相应的授权用户[master11]>grant replication slave replication client on *.* to [email protected]"%" identified by "123456";使用scalemon 用户监控读写分离服务器>grant select on mysql.* to [email protected]"%" identified by "123456";使用scalemax 用户监控登录的用户是否是数据库的授权用户[monitor13]maxscale --help#maxscale -f /etc/maxsacle.cnf     /启动服务#netstat -lnptu | grep :4006maxadmin -uamdin -pmariadb -P4009maxscale>list servers    /列出监控的服务器#killall maxscale  /杀死进程，停止服务验证[宿主机254]#mysql -h192.168.4.13 -P4006 -uuser -p123456>select @@hostname   /查看当前处于哪个数据库服务器

MySQL Database (vi)