mysql编译安装、多实例配置、主从复制
mysql编译安装及多实例部署
mysql多实例是在一台服务器上部署多个实例的mysql，各个mysql是相互隔离的，是共享统一的硬件资源和系统资源，每个实例都有自己的配置文件(可以是一个)、数据文件(可以是一个)、启动文件等。在服务器资源比较剩余的情况下用多实例比较合适，资源可以得到充分利用，但性能上会有点影响。一般多实例可以开1~4个实例。。。
1、mysql的编译安装需要安装的依赖包：
[root@moremysql mysql]# yum install ncurses-devel libaio-devel -y
2、编译mysql安装的话还需要mysql编译需要的cmake包：wget https://cmake.org/files/v2.8/cmake-2.8.8.tar.gz
安装cmake
cd cmake-2.8.8 ./configure gmake gmake install
创建mysql用户，用来管理mysql: useradd mysql -s /sbin/nologin -M
3、安装完依赖包和编译mysql需要的cmake包后，接着编译安装mysql
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \ -DMYSQL_DATADIR=/application/mysql-5.5.32/data \ -DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \ -DENABLED_LOCAL_INFILE=ON \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_FEDERATED_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \ -DWITH_FAST_MUTEXES=1 \ -DWITH_ZLIB=bundled \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_READLINE=1 \ -DWITH_EMBEDDED_SERVER=1 \ -DWITH_DEBUG=0
编译前的cmake参数设置没问题后，接下来进行编译安装，这个编译安装需要时间比较常，大概10分钟左右。
make && make install &
安装好mysql后对安装文件做软连接：
[root@moremysql mysql]# ln -s /application/mysql-5.5.32/ /application/mysql
2、创建多实例目录，为每个实例创建一个独立的目录，方便维护管理
目录结构如下：
[root@moremysql /]# tree /data
/data
├── 3306
│ ├── data
│ ├── my.cnf
│ └── mysql
└── 3307
├── data
├── my.cnf
└── mysql
编辑3306和3307实例的配置文件，这两个配置文件主要区别在于监听的端口和server-id不同
[client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /application/mysql datadir = /data/3306/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 #log_long_format #log-error = /data/3306/error.log #log-slow-queries = /data/3306/slow.log pid-file = /data/3306/mysql.pid log-bin = /data/3306/mysql-bin relay-log = /data/3306/relay-bin relay-log-info-file = /data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M #myisam_sort_buffer_size = 1M #myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 1 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3306/mysql_oldboy3306.err pid-file=/data/3306/mysqld.pid
[client] port = 3307 socket = /data/3307/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3307 socket = /data/3307/mysql.sock basedir = /application/mysql datadir = /data/3307/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M #long_query_time = 1 #log_long_format #log-error = /data/3307/error.log #log-slow-queries = /data/3307/slow.log pid-file = /data/3307/mysql.pid #log-bin = /data/3307/mysql-bin relay-log = /data/3307/relay-bin relay-log-info-file = /data/3307/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M #myisam_sort_buffer_size = 1M #myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 3 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3307/mysql_oldboy3307.err pid-file=/data/3307/mysqld.pid
编辑3306和3307实例的启动文件，这两个启动文件不同之处在于端口不同
#!/bin/sh ################################################ #this scripts is created by oldboy at 2007-06-09 #oldboy QQ:31333741 #site:http://www.etiantian.org #blog:http://oldboy.blog.51cto.com #oldboy trainning QQ group: 208160987 226199307 44246017 ################################################ #init port=3306 mysql_user="root" mysql_pwd="oldboy" CmdPath="/application/mysql/bin" mysql_sock="/data/${port}/mysql.sock" #startup function function_start_mysql() { if [ ! -e "$mysql_sock" ];then printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & else printf "MySQL is running...\n" exit fi } #stop function function_stop_mysql() { if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit else printf "Stoping MySQL...\n" ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown fi } #restart function function_restart_mysql() { printf "Restarting MySQL...\n" function_stop_mysql sleep 2 function_start_mysql } case $1 in start) function_start_mysql ;; stop) function_stop_mysql ;; restart) function_restart_mysql ;; *) printf "Usage: /data/${port}/mysql {start|stop|restart}\n" esac
#!/bin/sh ################################################ #this scripts is created by oldboy at 2007-06-09 #oldboy QQ:31333741 #site:http://www.etiantian.org #blog:http://oldboy.blog.51cto.com #oldboy trainning QQ group: 208160987 226199307 44246017 ################################################ #init port=3307 mysql_user="root" mysql_pwd="oldboy" CmdPath="/application/mysql/bin" mysql_sock="/data/${port}/mysql.sock" #startup function function_start_mysql() { if [ ! -e "$mysql_sock" ];then printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & else printf "MySQL is running...\n" exit fi } #stop function function_stop_mysql() { if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit else printf "Stoping MySQL...\n" ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown fi } #restart function function_restart_mysql() { printf "Restarting MySQL...\n" function_stop_mysql sleep 2 function_start_mysql } case $1 in start) function_start_mysql ;; stop) function_stop_mysql ;; restart) function_restart_mysql ;; *) printf "Usage: /data/${port}/mysql {start|stop|restart}\n" esac
对目录和文件授权，将多实例目录权限给成mysql。由于每个实例的mysql启动文件内容包含密码，所以要对mysql启动文件权限设置成700，只让root和mysql用户对mysql启动文件有权限。
[root@moremysql ~]# chown -R mysql.mysql /data [root@moremysql ~]# find /data/ -type f -name 'my.cnf'|xargs ls -l -rw-r--r-- 1 mysql mysql 1899 Oct 29 2013 /data/3306/my.cnf -rw-r--r-- 1 mysql mysql 1901 Oct 29 2013 /data/3307/my.cnf [root@moremysql ~]# find /data/ -type f -name 'mysql'|xargs ls -l -rw-r--r-- 1 mysql mysql 1307 Jul 15 2013 /data/3306/mysql -rw-r--r-- 1 mysql mysql 1307 Jul 21 2013 /data/3307/mysql [root@moremysql ~]# find /data/ -type f -name 'mysql'|xargs chmod 700 [root@moremysql ~]# find /data/ -type f -name 'mysql'|xargs ls -l -rwx------ 1 mysql mysql 1307 Jul 15 2013 /data/3306/mysql -rwx------ 1 mysql mysql 1307 Jul 21 2013 /data/3307/mysql
3、数据库初始化，初始化是为了生成系统mysql数据库和schema数据库
[root@moremysql ~]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3306/data/ --user=mysql
[root@moremysql ~]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307/data/ --user=mysql
4、启动mysql服务
[root@moremysql ~]# /data/3306/mysql start Starting MySQL... [root@moremysql ~]# /data/3307/mysql start Starting MySQL...
查看端口，检查mysql是否真的起来了
[root@moremysql ~]# netstat -lntup|grep mysqld tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26053/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 26772/mysqld
5、登录mysql，设置密码。对于多实例mysql登录，以后都要在登录命令后加上 -S /data/3306{3307}/mysql.sock
[root@moremysql ~]# mysql -S /data/3307/mysql.sock
修改密码：
[root@moremysql ~]# mysqladmin -uroot password 123456 -S /data/3306/mysql.sock [root@moremysql ~]# mysqladmin -uroot password 123456 -S /data/3307/mysql.sock
修改完密码在登录就需要用密码登录了
[root@moremysql ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock
注意：对于本地连接mysql要用 -S /data/3306/mysql.sock，而如果远程连接的话，就要用到-P 3306来指定端口了。比如先在本地创建一个可以远程连接的用户：
mysql> grant all on mysql.user to goser@'192.168.1.%' identified by '123456';
然后再远程数据库服务器上远程连接另一台数据库服务器
[root@mysqlserver ~]# mysql -ugoser -p123456 -h 192.168.1.106 -P 3306
6、停掉mysql服务的时候报错的解决办法
停掉mysql服务的报错信息：
[root@moremysql ~]# /data/3306/mysql stop Stoping MySQL... /application/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)'
这个错误表示设置的root密码和启动文件/data/3306/mysql中定义的密码不一致，所以要讲启动文件中的密码定义成root设置的密码：
[root@moremysql ~]# sed -i '13 s#oldboy#123456#g' /data/3306/mysql [root@moremysql ~]# sed -n '13p' /data/3306/mysql mysql_pwd="123456" [root@moremysql ~]# sed -i '13 s#oldboy#123456#g' /data/3307/mysql [root@moremysql ~]# sed -n '13p' /data/3307/mysql mysql_pwd="123456"
这时候再停掉mysql就没问题了
[root@moremysql ~]# /data/3306/mysql stop Stoping MySQL... [root@moremysql ~]# /data/3307/mysql stop Stoping MySQL...
mysql主从复制部署
mysql的主从复制是异步方式复制，是逻辑的sql语句级的复制。
同步时，主库有一个IO线程，从库有两个线程(IO线程和sql线程)，实现主从复制的必要条件为主库要开启binlog功能，binlog只记录对数据库有更改的sql语句。
1、修改配置文件，保证主库的log-bin开启的，主从数据库的server_id不同，无论是在一台服务器上部署多个实例，还是不同服务器上开启主从复制，主从数据库的server-id都要不同才行。
[root@moremysql ~]# egrep 'log-bin|server-id' /data/{3306,3307}/my.cnf
/data/3306/my.cnf:log-bin = /data/3306/mysql-bin
/data/3306/my.cnf:server-id = 1
/data/3307/my.cnf:#log-bin = /data/3307/mysql-bin
/data/3307/my.cnf:server-id = 3
配置文件修改完后要重启mysql才能生效，重启后最好确认配置文件的参数配置是否生效，用show variables来查看
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 1 row in set (0.00 sec)
2、登录3306实例主数据库，创建用于从库复制的账号
[root@moremysql ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123456'; mysql> flush privileges;
查看这个用于主从复制账号rep的权限
mysql> show grants for 'rep'@'192.168.1.%'; +--------------------------------------------------------------------------------------------------------------------------+ | Grants for rep@192.168.1.% | +--------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.1.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3、手动锁表与无需手动锁表的备份
有时候备份的时候需要锁表再备份，锁表后用户不可对表写的操作。。。
mysql> flush table with read lock;
查看master主库binlog位置点
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 482 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
开始备份主库，这时候备份会记下binlog的位置点，开始备份前不要退出锁表窗口，一旦退出的话锁表便失效，应该另开一个窗口备份。
[root@moremysql ~]# mysqldump -uroot -p123456 --events -S /data/3306/mysql.sock -A -B|gzip >/opt/mysql_3306_$(date +%F).gz
这时查看master的binlog位置点有没有变化，没问题再解锁
mysql> unlock tables;
上面的锁表备份是需要人工交互的，比如锁表操作及查看binlog位置等，这样很不方便。工作环境中基本上用下面的命令，自动锁表备份并记录master binlog位置。
[root@moremysql ~]# mysqldump -uroot -p123456 --events -S /data/3306/mysql.sock --master-data=2 -x -A -B -F>/opt/all_sql.sql
查看all_sql.sql文件，从中可以看到binlog文件及记录的位置，这是由于备份的时候有参数--master-data=2表示备份的时候记录binlog文件及位置点，2表示注释，如果改成1去除注释；-x备份时自动锁表。-F备份时刷新binlog,会生成新的binlog文件，这个参数类似与mysqladmin flush-log刷新binlog.
[root@moremysql ~]# less /opt/all_sql.sql -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107; -- -- Current Database: `mysql`
查看binlog文件，要用mysqlbinlog命令才能查看binlog文件的内容。可以看到binlog的记录的最后的位置信息及改成的命令。
[root@moremysql ~]# mysqlbinlog /data/3306/mysql-bin.000003
对3307从库导入3306主库的全量备份
[root@moremysql ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock</opt/all_sql.sql
我们知道从库要想同步主库的数据的话，从库每次会把主库的记录位置写到master.info文件中，会把主库的binlog文件写入到relay-log中，然后从库的线程sql会把relay-log中的变化转换成sql语句写入到数据库
这时候我们就要在从库上用CHANGE MASTER TO来生成master.info文件
mysql -uroot -p123456 -S /data/3307/mysql.sock<<EOF CHANGE MASTER TO MASTER_HOST='192.168.1.106', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107; EOF
这时候查看生成的master.info内容，包含了位置信息等。。
[root@moremysql ~]# cat /data/3307/data/master.info 18 mysql-bin.000003 107 192.168.1.106 rep 123456 3306 60 0 0 1800.000 0
生成master.info文件的时候同时生成了relay-bin文件
srwxrwxrwx 1 mysql mysql 0 Sep 16 15:46 mysql.sock -rw-rw---- 1 mysql mysql 107 Sep 16 19:29 relay-bin.000001 -rw-rw---- 1 mysql mysql 28 Sep 16 19:29 relay-bin.index -rw-rw---- 1 mysql mysql 51 Sep 16 19:29 relay-log.info
开启从库同步，查看同步状态
mysql> start slave; mysql> show slave status\G
这样在3306主库上对数据库的修改都会同步到从库上
在3306主库上删除test数据库 mysql> drop database test; 在3307从库上查看数据库，test也被删除了 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)
注意事项：
|
一般在做mysql数据库主从复制的时候是在晚上定时操作。 那么在mysqldump的时候就要指定--master-data=1，在从库导入这个备份文件的时候就会自动执行CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107; 人为就不需要再 CHANGE MASTER TO的时候再指定logbin文件和位置记录点，然后在从库上定义一个脚本让其晚上自动同步主库就行了 。
CHANGE MASTER TO MASTER_HOST='192.168.1.106', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='123456', |
4、mysql主从复制的错误解决
查看从数据库的slave状态，可以知道主从复制有没有错误，如果有错误的话会有错误码和错误信息
[root@moremysql ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e 'show slave status\G'
比如，从数据库已经常见了一个数据库goser，那么在主数据库上再创建同名的数据库goser的话，从数据库会报错，错误信息如下
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.106
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1018
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1078
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Error 'Can't create database 'goser'; database exists' on query. Default database: 'goser'. Query: 'create database goser'
Skip_Counter: 0
Exec_Master_Log_Pos: 932
Relay_Log_Space: 1314
Until_Condition: None
这里的last_errno:1007就是错误码，last_error就是错误信息
解决这个错误办法为：
stop slave; 临时先关掉同步 set global sql_slave_skip_counter=1; 让指针向下移一步 start slave; 再开启同步
这个办法只是解决当次错误，如果下次还出现这个错误的话，同样还要这样解决，比较麻烦。而且这个错误是可以忽略的，所以要用下面的方法一劳永逸，将从数据库的mysql配置文件的slave-skip-errors更新下
这个参数表示可以忽略的错误码，可以将上面的错误码添加进去，然后再重启mysql服务，下次再出现这个错误从服务器就可直接跳掉。
[root@moremysql ~]# vim /data/3307/my.cnf slave-skip-errors = 1032,1062,1007,1008
[root@moremysql ~]# /data/3306/mysql stop Stoping MySQL... [root@moremysql ~]# /data/3306/mysql start Starting MySQL... [root@moremysql ~]# /data/3307/mysql stop Stoping MySQL... [root@moremysql ~]# /data/3307/mysql start Starting MySQL...
mysql的错误码有：
1005：创建表失败 1006：创建数据库失败 1007：数据库已存在，创建数据库失败--------------可以忽略 1008：数据库不存在，删除数据库失败--------------可以忽略 1009：不能删除数据库文件导致删除数据库失败 1010：不能删除数据目录导致删除数据库失败 1011：删除数据库文件失败 1012：不能读取系统表中的记录 1020：记录已被其他用户修改 1021：硬盘剩余空间不足，清加大硬盘可用空间 1022：关键字重复，更改记录失败 1023：关闭时发生错误 1024：读文件错误 1025：更改名字时发生错误 1026：写文件错误 1032：记录不存在-------------------------------可以忽略 1036：数据表是只读的，不能对它进行修改 1037：系统内存不足，请重启数据库或重启服务器 1038：用于排序的内存不足，清增大排序缓冲区 1040：已到达数据库的最大连接数，请加大数据库可用连接数 1041：系统内存不足 1042：无效的主机名 1043：无效连接 1044：当前用户没有访问数据库的权限 1045：不能连接数据库，用户名或密码错误 1048：字段不能为空 1049：数据库不存在 1050：数据表已存在 1051：数据表不存在 1054：字段不存在 1062：字段值重复，入库失败----------------------可以忽略 1065：无效的SQL语句，SQL语句为空 1081：不能建立Socket连接 1114：数据表已满，不能容纳任何记录 1116：打开的数据表太多 1129：数据库出现异常，请重启数据库 1130：连接数据库失败，没有连接数据库的权限 1133：数据库用户不存在 1141：当前用户无权访问数据库 1142：当前用户无权访问数据表 1143：当前用户无权访问数据表中的字段 1146：数据表不存在 1147：未定义用户对数据表的访问权限 1149：SQL语句语法错误 1158：网络错误，出现读错误，请检查网络连接状况 1159：网络错误，读超时，请检查网络连接状况 1160：网络错误，出现写错误，请检查网络连接状况 1161：网络错误，写超时，请检查网络连接状况 1169：字段值重复，更新记录失败 1177：打开数据表失败 1180：提交事务失败 1181：回滚事务失败 1203:当前用户和数据库建立的连接已到达数据库的最大连接数，请增加可用的数据库连接数或重启数据库 1205：加锁超时 1211：当前用户没有创建用户的权限 1216：外键约束检查失败，更新子表记录失败 1217：外键约束检查失败，删除或修改主表记录失败 1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器 1227：权限不足，您无权进行此操作 1235：MySQL版本过低，不具有本功能
5、关于级联的主从复制及双写同步
对于一主一从的主从复制企业部署很常用，也可以做一主多从的主从复制，这个部署和上面的方式相同，就是多加从数据库而已。
当然也可以做级联的主从复制方式，如下图：
也就是说只要在B上的my.cnf配置文件中添加log-slave-updates选项即可，并且保证每个mysql服务的server-id不同
双写复制：
要实现mysql的双写功能的话，要保证每个服务器的记录不能重复，设计的思路为：在A服务上记录1 3 5 7...记录，在B服务上记录2 4 6 8...的记录，让主键id自增
在A,B服务上的配置文件更改如下：
[root@moremysql opt]# cat /data/3306/my.cnf [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /application/mysql datadir = /data/3306/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 pid-file = /data/3306/mysql.pid #log-bin = /data/3306/mysql-bin relay-log = /data/3306/relay-bin relay-log-info-file = /data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M #expire_logs_days = 7 #_____m-m m1 start_____ auto_increment_increment = 2 auto_increment_offset = 1 log-slave-updates log-bin = /data/3306/mysql-bin expire_logs_days = 7 #_____m-m m1 start_____ key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M #myisam_sort_buffer_size = 1M #myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 1 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3306/mysql_oldboy3306.err pid-file=/data/3306/mysqld.pid
[root@moremysql opt]# cat /data/3307/my.cnf [client] port = 3307 socket = /data/3307/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3307 socket = /data/3307/mysql.sock basedir = /application/mysql datadir = /data/3307/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M #long_query_time = 1 #log_long_format #log-error = /data/3307/error.log #log-slow-queries = /data/3307/slow.log pid-file = /data/3307/mysql.pid #log-bin = /data/3307/mysql-bin relay-log = /data/3307/relay-bin relay-log-info-file = /data/3307/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M #expire_logs_days = 7 #_____m-m m2 start_____ auto_increment_increment = 2 auto_increment_offset = 2 log-slave-updates log-bin = /data/3307/mysql-bin expire_logs_days = 7 #_____m-m m2 start_____ key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M #myisam_sort_buffer_size = 1M #myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062,1007,1008 replicate-ignore-db=mysql server-id = 3 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3307/mysql_oldboy3307.err pid-file=/data/3307/mysqld.pid
配置文件修改后重启mysql服务，然后在A，B上各自全备数据库
[root@moremysql opt]# mysqldump -uroot -p123456 --events -S /data/3306/mysql.sock -A -B|gzip >/opt/mysql_3306_$(date +%F).sql.gz [root@moremysql opt]# mysqldump -uroot -p123456 --events -S /data/3307/mysql.sock -A -B|gzip >/opt/mysql_3307_$(date +%F).sql.gz
然后将全备的文件scp分别推送到对方的服务器上，解压后，分别导入对方的全备数
[root@moremysql opt]# mysql -uroot -p123456 -S /data/3306/mysql.sock<mysql_3307_2017-09-17.sql [root@moremysql opt]# mysql -uroot -p123456 -S /data/3307/mysql.sock<mysql_3306_2017-09-17.sql
接下来在各自的数据库中CHANGE MASTER TO对方的数据信息
CHANGE MASTER TO MASTER_HOST='192.168.1.106', MASTER_PORT=3307, MASTER_USER='rep', MASTER_PASSWORD='123456'
CHANGE MASTER TO MASTER_HOST='192.168.1.106', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='123456'
各自开启slave 并查看slave信息
start slave show slave status\G
正常后创建表，插入数据，检查A，B插入的数据是不是1 3 5 2 4 6等
创建class数据库
create database class;
创建userinfo表 create table `userinfo` ( `id` bigint(12) not null auto_increment, `name` varchar(12) not null, primary key (`id`) );
分别在A，B上向userinfo表中插入数据，实现的结果和设计的思路相同，只不过数据的id可能会不连续。。。
mysql> select * from userinfo; +----+----------+ | id | name | +----+----------+ | 1 | goser | | 3 | hello | | 5 | world | | 6 | zhangsan | | 8 | lisi | | 10 | wangwu | +----+----------+ 6 rows in set (0.00 sec)
疑问点： 为什么B上没有创建rep用户作为主从复制账号，B也能将数据同步到A中能？这是由于在A创建好grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123456';后已经同步到B上，所以B上没必要再创建rep账号。。。
mysql主从复制集群架构的数据备份策略:
主从复制的缺点：当主服务上执行了drop,delect等操作，从服务器也会同步drop,delect操作，这样找回删除前的数据就比较麻烦了 。而且业务中对数据库是读多写少，这时候可以采用读写分离方式来部署数据库，前端的程序员可以根据sql语句选择读定位到从服务器ip,写的话定位到主服务器的ip，比如在java的hibernate或mybatis连接数据库时可以指定。。。
在高并发业务备份场景中，可以选择一台从库上备份，这个从库和主库更新更接近，比如上图中的slave服务器5,。需求如下：
（1）选择一个不对外服务的从库，专门做数据备份用，如上图的slave服务器5
（2）开启从库的binlog功能
（3）备份时可以选择只停止SQL线程，这样就停止了应用sql语句到数据库的操作，让IO线程保持在工作状态，执行命令stop slave sql_thread;来停止sql线程，备份方式可以采用mysqldump逻辑备份或直接物理备份，如cp、tar 数据库的/data目录，如果备份的数据量大于50G，这样用mysqldump备份就不合适了，就要用到cp、tar命令直接物理备份，同时将全备和binlog发送到备份服务器上留存。
那么在mysql读写分离环境中，如何授权web用户的权限呢？
设置从库为read-only，这个参数要加到从库的数据库配置文件中就行了
设置了从库的只读，但不影响root super用户的操作，同时也不影响主从复制，只是对web用户访问从库只能读，无论对web用户设置权限多大都只有读的权限
比如在主库上对web用户设置的权限为：grant insert,update,delete,create,select on class.* to web@'192.168.1.%' identified by '123';
那么通过主从复制方式这个web用户已经同步到从库上了，但是从库上又设置了read-only，那么web用户在从库上的权限也仅仅是只读权限。
把备选主库提升为主库的操作步骤(提升上图的slave服务器5)：
|
1、确保所有relay log全部更新完毕 如果从库和提升为主库的从库数据不一致，应将提升主库的从库全备导入到各个从库，记下提升主库的从的binlog和位置点 |
浙公网安备 33010602011771号