vlambda博客
学习文章列表

[mysqld_multi]mysqld = /usr/bin/mysqld_safemysqladmin = /usr/bin/mysqladminuser = mysql[mysqld1]socket = /var/lib/mysql/mysql.sockport = 3306pid-file = /var/lib/mysql/mysql.piddatadir = /var/lib/mysql/user = mysql[mysqld2]socket = /mnt/data/db1/mysql.sockport = 3302pid-file = /mnt/data/db1/mysql.piddatadir = /mnt/data/db1/user = mysqlskip-name-resolvserver-id=10default-storage-engine=innodbinnodb_buffer_pool_size=512Minnodb_additional_mem_pool=10Mdefault_character_set=utf8character_set_server=utf8#read-onlyrelay-log-space-limit=3Gexpire_logs_day=20

mysqld_multi --config-file=/data/mysql/my_multi.cnf start 1,2

[root@localhost /]# tree /data/data├── 3306│ ├── data #3306实例的数据目录│ ├── my.cnf #3306实例的配置文件│ └── mysql #3306实例的启动文件└── 3307 ├── data #3307实例的数据目录 ├── my.cnf #3307实例的配置文件 └── mysql #3307实例的启动文件
4 directories, 4 files

[root@localhost ~]# yum -y install ncurses-devel libaio-devel[root@localhost ~]# rpm -qa ncurses-devel libaio-develncurses-devel-5.7-4.20090207.el6.x86_64libaio-devel-0.3.107-10.el6.x86_64

[root@localhost ~]# ls -lh cmake-2.8.6.tar.gz -rw-r--r-- 1 root root 5.4M 7月 19 20:43 cmake-2.8.6.tar.gz #此软件需提前准备[root@localhost ~]# tar xf cmake-2.8.6.tar.gz -C /usr/src/[root@localhost ~]# cd /usr/src/cmake-2.8.6/[root@localhost cmake-2.8.6]# ./configure[root@localhost cmake-2.8.6]# gmake && gmake install[root@localhost cmake-2.8.6]# which cmake

[root@localhost ~]# useradd -s /sbin/nologin -M mysql[root@localhost ~]# id mysqluid=500(mysql) gid=500(mysql) 组=500(mysql)

[root@localhost ~]# tar xf mysql-5.5.22.tar.gz -C /usr/src/[root@localhost ~]# cd /usr/src/mysql-5.5.22/[root@localhost mysql-5.5.22]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.22 \> -DMYSQL_DATADIR=/usr/local/mysql-5.5.22/data \ #数据存放目录> -DMYSQL_UNIX_ADDR=/usr/local/mysql-5.5.22/tmp/mysql.sock \ #MySQL进程间通信的套接字位置> -DDEFAULT_CHARSET=utf8 \ #默认字符集为utf8> -DDEFAULT_COLLATION=utf8_general_ci \ #默认字符集排序规则> -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \ #额外的字符集支持> -DENABLED_LOCAL_INFILE=ON \ #是否启用加载本地数据> -DWITH_INNOBASE_STORAGE_ENGINE=1 \ #静态编译innodb存储引擎到数据库> -DWITH_FEDERATED_STORAGE_ENGINE=1 \ #静态编译FEDERATED存储引擎到数据库> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #静态编译blackhole存储引擎到数据库> -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ #不编译EXAMPLE存储引擎到数据库> -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \ #不支持数据库分区> -DWITH_FAST_MUTEXES=1 \> -DWITH_ZLIB=bundled \ #zlib压缩模式> -DENABLED_LOCAL_INFILE=1 \ #是否启用本地的LOCAL_INFILE> -DWITH_READLINE=1 \ #使用捆绑的readline> -DWITH_EMBEDDED_SERVER=1 \ #是否要建立嵌入式服务器> -DWITH_DEBUG=0 #禁用DEBUG（开启影响性能）# 提示：编译时可配置的选项很多，具体可参考官方文档[root@localhost mysql-5.5.22]# make && make install

[root@localhost mysql-5.5.22]# ln -s /usr/local/mysql-5.5.22 /usr/local/mysql[root@localhost mysql-5.5.22]# ls /usr/local/mysqlbin data include lib mysql-test scripts sql-benchCOPYING docs INSTALL-BINARY man README share support-files
如果上述操作未出现错误，查看/usr/local/mysql目录下有内容，则MySQL5.5.22源代码包采用cmake方式的安装就算成功了。

[root@localhost ~]# mkdir -p /data/{3306,3307}/data[root@localhost ~]# tree /data//data/├── 3306 #3306实例目录│ └── data #3306实例的数据文件目录├── 3307 #3307实例目录 └── data #3307实例的数据文件目录

4 directories, 0 files

[root@localhost mysql]# ls -l support-files/my*.cnf-rw-r--r-- 1 root root 4751 7月 19 21:33 support-files/my-huge.cnf-rw-r--r-- 1 root root 19805 7月 19 21:33 support-files/my-innodb-heavy-4G.cnf-rw-r--r-- 1 root root 4725 7月 19 21:33 support-files/my-large.cnf-rw-r--r-- 1 root root 4736 7月 19 21:33 support-files/my-medium.cnf-rw-r--r-- 1 root root  2900 7月  19 21:33 support-files/my-small.cnf

[root@localhost ~]# cat /data/3306/my.cnf[client]port = 3306socket = /data/3306/mysql.sock[mysqld]user = mysqlport = 3306socket = /data/3306/mysql.sockbasedir = /usr/local/mysqldatadir = /data/3306/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_open_cache = 614external-locking = FALSEmax_allowed_packet = 8M#binlog_cache_size = 1M#max_heap_table_size = 64M#read_buffer_size = 2M#read_rnd_buffer_size = 16Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#ft_min_word_len = 4#default-storage-engine = MYISAMthread_stack = 192Ktransaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2M#log-bin=mysql-bin#binlog_format=mixed#slow_query_loglong_query_time = 1pid-file = /data/3306/mysql.pidrelay-log = /data/3306/relay-binrelay-log-info-file = /data/3306/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mkey_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1Mlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db = mysql
server-id = 1
#key_buffer_size = 32M#bulk_insert_buffer_size = 64M#myisam_sort_buffer_size = 128M#myisam_max_sort_file_size = 10G#myisam_repair_threads = 1#myisam_recoverinnodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4#innodb_write_io_threads = 8#innodb_read_io_threads = 8innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0
[mysqldump]quickmax_allowed_packet = 2M
[mysql]no-auto-rehash
#[myisamchk]#key_buffer_size = 512M#sort_buffer_size = 512M#read_buffer = 8M#write_buffer = 8M#[mysqlhotcopy]#interactive-timeout
[mysqld_safe]log-error = /data/3306/mysql_yunjisuan3306.errpid-file = /data/3306/mysqld.pid

[root@localhost ~]# tree /data/data├── 3306│ ├── data│ └── my.cnf #这个就是3306实例的配置文件└── 3307 ├── data └── my.cnf #这个就是3307实例的配置文件
4 directories, 2 files

[root@localhost ~]# cat /data/3306/mysql#!/bin/bash################################################this scripts is created by Mr.chen at 2016-06-25
port=3306mysql_user="root"mysql_pwd="" #这里需要修改为用户的实际密码
CmdPath="/usr/local/mysql/bin"mysql_sock="/data/${port}/mysql.sock"
#startup functionfunction_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 functionfunction_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 functionfunction_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

[root@localhost ~]# tree /data/data├── 3306│ ├── data│ ├── my.cnf #3306实例的配置文件│ └── mysql #3306实例的启动文件└── 3307 ├── data ├── my.cnf #3307实例的配置文件 └── mysql #3307实例的启动文件
4 directories, 4 files

[root@localhost ~]# chown -R mysql.mysql /data[root@localhost ~]# find /data -name "mysql" | xargs ls -l-rw-r--r--. 1 mysql mysql 1039 Jul 20 19:33 /data/3306/mysql-rw-r--r--. 1 mysql mysql 1039 Jul 20 19:34 /data/3307/mysql

[root@localhost ~]# find /data -name "mysql" | xargs chmod 700[root@localhost ~]# find /data -name "mysql" | xargs ls -l-rwx------. 1 mysql mysql 1039 Jul 20 19:33 /data/3306/mysql-rwx------. 1 mysql mysql 1039 Jul 20 19:34 /data/3307/mysql

[root@localhost ~]# ls /usr/local/mysql/bin/mysql/usr/local/mysql/bin/mysql

[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile#注意，echo后边是单引号，双引号的话变量内容会被解析掉。[root@localhost ~]# tail -1 /etc/profileexport PATH=/usr/local/mysql/bin:$PATH[root@localhost ~]# source /etc/profile#执行source使上一行添加到/etc/profile中，内容直接生效#以上命令的用途为定义mysql全局路径，实现在任意路径执行mysql命令

[root@localhost scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql[root@localhost scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql
提示：--basedir=/usr/local/mysql为MySQL的安装路径，--datadir为不同的实例数据目录

[root@localhost scripts]# tree /data
#以下省略若干...
5.8 启动MySQL多实例的命令[root@localhost scripts]# /data/3306/mysql startStarting MySQL....[root@localhost scripts]# /data/3307/mysql startStarting MySQL....root@localhost scripts]# netstat -antup | grep 330tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 24743/mysqld tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      24020/mysqld

[root@localhost ~]# echo "#mysql multi instances" >> /etc/rc.local [root@localhost ~]# echo "/data/3306/mysql start" >> /etc/rc.local[root@localhost ~]# echo "/data/3307/mysql start" >> /etc/rc.local [root@localhost ~]# tail -3 /etc/rc.local #mysql multi instances/data/3306/mysql start/data/3307/mysql start
#这里一定要确保MySQL脚本可执行~

[root@localhost ~]# mysql -S /data/3306/mysql.sock #直接敲就进来了，而且身份还是root。但是多了-S /data/3306/mysql.sock,用于区别登陆不同的实例Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.22 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; #查看当前所有的数据库+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4 rows in set (0.00 sec)mysql> select user(); #查看当前的登陆用户+----------------+| user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)

mysql -S /data/3306/mysql.sockmysql -S /sata/3307/mysql.sock

/data/3306/mysql stop/data/3307/mysql start

[root@localhost ~]# mysqladmin -u root -S /data/3306/mysql.sock password '123123' #为mysql设置密码[root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock #无法直接登陆了Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.5.22 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
#提示：3307实例设置方法和3306实例相同，只是连接时的mysql，sock路径不同，这已经提醒多次，大家要注意

#登陆3306实例的命令如下：mysql -uroot -p123123 -S /data/3306/mysql.sock#登陆3307实例的命令如下：mysql -uroot -p123123 -S /data/3307/mysql.sock
提示： 基础弱的同学，在测试时尽量保证多实例的密码相同，可以减少麻烦，后面还原数据库时会覆盖密码！ 若要重启多实例数据库，也需要进行相应的如下配置。再重启数据库前，需要调整不同实例启动文件里对应的数据库密码。[root@localhost ~]# vim /data/3306/mysql[root@localhost ~]# sed -n '7p' /data/3306/mysql #这是之前mysql多实例启动脚本里mysql的登陆密码变量mysql_pwd=""[root@localhost ~]# sed -i '7 s#""#"123123"#' /data/3306/mysql[root@localhost ~]# sed -n '7p' /data/3306/mysqlmysql_pwd="123123" #修改成实际的登录密码[root@localhost ~]# sed -n '7p' /data/3307/mysqlmysql_pwd=""[root@localhost ~]# sed -i '7 s#""#"123123"#' /data/3307/mysql[root@localhost ~]# sed -n '7p' /data/3307/mysqlmysql_pwd="123123"

/data/3306/mysql stop

/data/3306/mysql start

mkdir -p /data/3308/data\cp /data/3306/my.cnf /data/3308/\cp /data/3306/mysql /data/3308/sed -i 's#3306#3308#g' /data/3308/my.cnfsed -i 's#server-id = 1#server-id = 8#g' /data/3308/my.cnfsed -i 's#3306#3308#g' /data/3308/mysqlchown -R mysql:mysql /data/3308chmod 700 /data/3308/mysqlcd /usr/local/mysql/scripts./mysql_install_db --datadir=/data/3308/data --basedir=/usr/local/mysql --user=mysqlchown -R mysql:mysql /data/3308egrep "server-id|log-bin" /data/3308/my.cnf/data/3308/mysql start
netstat -antup | grep 3308#提示：最好把server-id按照IP地址最后一个小数点的数字设置#成功标志：多了一个启动的端口3308

[mysqld]log-bin=/data/3306/mysql-bin
提示： 有些同学把log-bin放在了配置文件结尾，而不是[mysqld]标识后，从而导致配置复制不成功。

[root@localhost ~]# ss -antup | grep 330tcp LISTEN 0 128 *:3307 *:* users:(("mysqld",3910,11))tcp LISTEN 0 128 *:3306 *:* users:(("mysqld",2450,11))
提示： 这里把3306实例作为主库，3307实例作为从库，如果根据前面的内容配置了MySQL多实例环境，直接开启多实例环境使用即可。

[mysqld]server-id = 1 #用于同步的每台机器或实例server-id都不能相同log-bin = /data/3306/mysql-bin #binlog日志的位置
提示：上面的两个参数要放在my.cnf中的[mysqld]模块下，否则会出错。 不同实例间server-id的值不可以重复 要先在my.cnf配置文件中查找相关参数，并按要求修改。若发现不存在，再添加参数，切记，参数不能重复。 修改my.cnf配置后，需要重启动数据库，命令为：/data/3306/mysql restart ,注意要确认真正重启了。

root@localhost ~]# egrep "server-id|log-bin" /data/3306/my.cnfserver-id = 1log-bin=/data/3306/mysql-bin        #log-bin后面也可以不带等号内容，MySQL会使用默认日志

[root@localhost ~]# /data/3306/mysql restartRestarting MySQL...Stoping MySQL...Starting MySQL....

[root@localhost ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock #登陆3306实例Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'server_id'; #查看MySQL的系统变量（like类似于grep过滤）+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id | 1 | #配置的server_id为1+---------------+-------+1 row in set (0.00 sec)
mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | ON | #binlog功能已开启+---------------+-------+1 row in set (0.00 sec)
mysql> 
#这样，binlog功能就开启了。

[root@localhost ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock2)建立用于从库复制的账号yunjisuan，命令如下：mysql> grant replication slave on *.* to 'yunjisuan'@'192.168.0.%' identified by 'yunjisuan123';Query OK, 0 rows affected (0.00 sec)
#语句说明：1）replication slave为mysql同步的必须权限，此处不要授权all权限2）*.* 表示所有库所有表，也可以指定具体的库和表进行复制。例如yunjisuan.test中，yunjisuan为库名，test为表名3）'yunjisuan'@'192.168.0.%' yunjisuan为同步账号。192.168.0.%为授权主机网段，使用了%表示允许整个192.168.0.0网段可以用yunjisuan这个用户访问数据库4）identified by 'yunjisuan123';  yunjisuan123为密码，实际环境下设置的复杂些为好

mysql> flush privileges; #刷新权限Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;+-----------+-------------+| user | host |+-----------+-------------+| root | 127.0.0.1 || yunjisuan | 192.168.0.% | #出现这行表示复制账号已经配置好了| root | ::1 || | localhost || root | localhost |+-----------+-------------+5 rows in set (0.00 sec)
#说明：MySQL里的授权用户是以数据表格的形式存储在mysql这个库的user表里。

mysql> select user,host from mysql.user where user='yunjisuan'; #where是SQL查询语句的条件+-----------+-------------+| user | host |+-----------+-------------+| yunjisuan | 192.168.0.% |+-----------+-------------+1 row in set (0.00 sec)
mysql> show grants for yunjisuan@'192.168.0.%'; #查看账号的授权情况+--------------------------------------------------------------------------------------------------------------------------------+| Grants for [email protected].% |+--------------------------------------------------------------------------------------------------------------------------------+| GRANT REPLICATION SLAVE ON *.* TO 'yunjisuan'@'192.168.0.%' IDENTIFIED BY PASSWORD '*A2CC7FA422EF5A7CB098FEA7732C1F78CDC32F67' | #结果显示授权正确+--------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

mysql> flush table with read lock;Query OK, 0 rows affected (0.00 sec)

提示： 在引擎不同的情况下，这个锁表命令的时间会受下面参数的控制。锁表时，如果超过设置时间不操作会自动解锁。 默认情况下自动解锁的时长参数值如下：

mysql> show variables like '%timeout%';+----------------------------+----------+| Variable_name | Value |+----------------------------+----------+| connect_timeout | 10 || delayed_insert_timeout | 300 || innodb_lock_wait_timeout | 120 || innodb_rollback_on_timeout | OFF || interactive_timeout | 28800 | #自动解锁时间受本参数影响| lock_wait_timeout | 31536000 || net_read_timeout | 30 || net_write_timeout | 60 || slave_net_timeout | 3600 || wait_timeout | 28800 | #自动解锁时间受本参数影响+----------------------------+----------+10 rows in set (0.00 sec)
#提示：有关这两个参数，请同学们自行测试

mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 345 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

或者新开一个命令行窗口，用如下命令查看锁表后的主库binlog位置点信息：[root@localhost ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "show master status"+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 533 | | |+------------------+----------+--------------+------------------+

[root@localhost ~]# mkdir -p /server/backup[root@localhost ~]# mysqldump -uroot -p123123 -S /data/3306/mysql.sock --events -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
#注意：-A表示备份所有库；-B表示增加use DB和 drop 等（导库时会直接覆盖原有的）
[root@localhost ~]# ll /server/backup/mysql_bak.2017-07-21.sql.gz -rw-r--r--. 1 root root 137344 Jul 21 10:17 /server/backup/mysql_bak.2017-07-21.sql.gz
#为了确保导出数据期间，数据库没有数据插入，导库完毕可以再次检查主库状态信息，结果如下：
[root@localhost ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "show master status"+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 533 | | |+------------------+----------+--------------+------------------+
提示：若无特殊情况，binlog文件及位置点和锁表后导出数据前是一致的，即没有变化。#导出数据完毕后，解锁主库，恢复可写，命令如下.因为主库还要对外提供服务，不能一直锁定不让用户访问。
mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)
#可能会有同学因为锁表后的binlog位置问题犯迷糊，实际上做从库前，无论主库更新了多少数据，最后从库都可以从上面show master status的位置很快赶上主库的进度。

[root@localhost ~]# ll /server/backup/mysql_bak.2017-07-21.sql.gz -rw-r--r--. 1 root root 137344 Jul 21 10:17 /server/backup/mysql_bak.2017-07-21.sql.gz

[mysqld]server-id = 3 #调整等号后的数值，和任何一个数据库实例都不同
提示： 上面两参数要放在my.cnf中的[mysqld]模块下，否则会出错。server-id的值不能和任何MySQL实例重复。 要先在文件中查找相关参数按要求修改。若发现不存在，再添加参数，切记，参数不能重复。 修改my.cnf配置后需要重启数据库，命令为：/data/3307/mysql restart,注意要确认真正重启了。

[root@localhost ~]# egrep "server-id|log-bin" /data/3307/my.cnfserver-id = 3

[root@localhost ~]# /data/3307/mysql restartRestarting MySQL...Stoping MySQL...Starting MySQL....[root@localhost ~]# ss -antup | grep 3307tcp    LISTEN     0      128                    *:3307                  *:*      users:(("mysqld",5659,11))

[root@localhost ~]# mysql -uroot -p123123 -S /data/3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.22 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | OFF |+---------------+-------+1 row in set (0.00 sec)
mysql> show variables like 'server_id';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id | 3 |+---------------+-------+1 row in set (0.00 sec)
mysql>

[root@localhost ~]# cd /server/backup/[root@localhost backup]# ls -ltotal 136-rw-r--r--. 1 root root 137344 Jul 21 10:17 mysql_bak.2017-07-21.sql.gz[root@localhost backup]# gzip -d mysql_bak.2017-07-21.sql.gz [root@localhost backup]# lltotal 496-rw-r--r--. 1 root root 506730 Jul 21 10:17 mysql_bak.2017-07-21.sql[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock <mysql_bak.2017-07-21.sql #这是把数据还原到3307实例的命令
#提示：如果备份时使用了-A参数，则在还原数据到3307实例时，登陆3307实例的密码也会和3306主库的一致，因为3307实例的授权表MySQL也被覆盖了。

CHANGE MASTER TOMASTER_HOST='192.168.0.200', #这里是主库的IPMASTER_PORT=3306, #这里是主库的端口，从库端口可以和主库不同MASTER_USER='yunjisuan', #这里是主库上建立的用于复制的用户yunjisuanMASTER_PASSWORD='yunjisuan123', #这里是yunjisuan用户的密码MASTER_LOG_FILE='mysql-bin.000001', #这里是show master status时查看到的二进制日志文件名称，注意不能多空格MASTER_LOG_POS=533; #这里是show master status时查看到的二进制日志偏移量，注意不能多空格
#提示：字符串用单引号括起来，数值不用引号，注意内容前后不能有空格。

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.200',MASTER_PORT=3306,MASTER_USER='yunjisuan',MASTER_PASSWORD='yunjisuan123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=533;
#提示：这个步骤的参数一定不能错，否则，数据库复制配置会失败
上述操作的原理实际上是把用户密码等信息写入从库新的master.info文件中[root@localhost backup]# ll /data/3307/data/master.info -rw-rw----. 1 mysql mysql 90 Jul 21 11:31 /data/3307/data/master.info[root@localhost backup]# cat /data/3307/data/master.info18mysql-bin.000001 #这里是show master status时查看的二进制日志文件名称533 #这里是show master status时查看的二进制日志偏移量192.168.0.200 #这里是主库的IPyunjisuan #这里是主库上建立的用于复制的用户yunjisuanyunjisuan123 #这里是yunjisuan用户的密码3306 #这里是主库的端口60
0...以下省略若干...

[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "start slave"[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show slave status\G"*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.200 Master_User: yunjisuan Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 533 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB:  Replicate_Ignore_DB: mysql Replicate_Do_Table:  Replicate_Ignore_Table:  Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:  Last_Errno: 0 Last_Error:  Skip_Counter: 0 Exec_Master_Log_Pos: 533 Relay_Log_Space: 403 Until_Condition: None Until_Log_File:  Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File:  Master_SSL_CA_Path:  Master_SSL_Cert:  Master_SSL_Cipher:  Master_SSL_Key:  Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error:  Last_SQL_Errno: 0 Last_SQL_Error:  Replicate_Ignore_Server_Ids:  Master_Server_Id: 1

[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show slave status\G" | egrep "IO_Running|SQL_Running|Seconds_Behind_Master" Slave_IO_Running: Yes Slave_SQL_Running: Yes        Seconds_Behind_Master: 0

[root@localhost backup]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "create database benet"[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show databases"+--------------------+| Database |+--------------------+| information_schema || benet || mysql || performance_schema || test |+--------------------+[root@localhost backup]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "create database yunjisuan"[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show databases"+--------------------+| Database |+--------------------+| information_schema || benet || mysql || performance_schema || test || yunjisuan |+--------------------+[root@localhost backup]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "drop database yunjisuan"[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show databases"+--------------------+| Database |+--------------------+| information_schema || benet || mysql || performance_schema || test |+--------------------+
#根据测试可以判断，主从库是同步的。

mysql> show processlist\G*************************** 1. row *************************** Id: 1 User: root Host: localhost db: NULLCommand: Query Time: 0 State: NULL Info: show processlist*************************** 2. row *************************** Id: 5 User: yunjisuan Host: 192.168.0.200:42008 db: NULLCommand: Binlog Dump Time: 267 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL2 rows in set (0.00 sec)
#提示：上述状态的意思是线程已经从binlog日志读取所有更新，并已经发送到了从数据库服务器。线程目前为空闲状态，等待由主服务器上二进制日志中的新事件更新。

mysql> show processlist\G*************************** 1. row *************************** Id: 3 User: root Host: localhost db: NULLCommand: Query Time: 0 State: NULL Info: show processlist*************************** 2. row *************************** Id: 8 User: system user Host:  db: NULLCommand: Connect Time: 659 State: Waiting for master to send event Info: NULL*************************** 3. row *************************** Id: 9 User: system user Host:  db: NULLCommand: Connect Time: 511 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL3 rows in set (0.00 sec)

mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.200 Master_User: yunjisuan Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 544 Relay_Log_File: relay-bin.000010 Relay_Log_Pos: 336 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 'yunjisuan'; database exists' on query. Default database: 'yunjisuan'. Query: 'create database yunjisuan' Skip_Counter: 0 Exec_Master_Log_Pos: 451 Relay_Log_Space: 810 Until_Condition: None Until_Log_File:  Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File:  Master_SSL_CA_Path:  Master_SSL_Cert:  Master_SSL_Cipher:  Master_SSL_Key:  Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error:  Last_SQL_Errno: 1007 Last_SQL_Error: Error 'Can't create database 'yunjisuan'; database exists' on query. Default database: 'yunjisuan'. Query: 'create database yunjisuan' Replicate_Ignore_Server_Ids:  Master_Server_Id: 11 row in set (0.00 sec)

mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> set global sql_slave_skip_counter=1; #将sql线程同步指针向下移动一个，如果多次不同步，可以重复操作Query OK, 0 rows affected (0.00 sec)
mysql> start slave;Query OK, 0 rows affected (0.00 sec)

对于普通的互联网业务，上述的移动指针的操作带来的问题不是很大。当然，要在确认不影响公司业务的前提下。 若是在企业场景下，对当前业务来说，解决主从同步比主从不一致更重要，如果主从数据一致也是很重要的，那就再找个时间恢复这个从库。 是主从数据不一致更重要，还是保持主从同步持续状态更重要，要根据业务选择。这样Slave就会与Master同步了，主要关键点如下： Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 #0表示已经同步状态
#提示：set global sql_slave_skip_counter=n;     #n取值>0,忽略执行N个更新。

[root@localhost ~]# grep slave-skip /data/3306/my.cnfslave-skip-errors = 1032,1062
#提示：类似由于入库重复导致的失败可以忽略，其他情况是不是可以忽略需要根据不同公司的具体业务来评估。

 log-slave-updates #必须要有这个参数 log-bin = /data/3307/mysql-bin    expire_logs_days = 7    #相当于find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 | xargs rm -f

使用killall mysqld或mysqladmin -uroot -p123123 -S /data/3307/mysql.sock shutdownmysqld_safe --defaults-file=/data/3307/my.cnf --read-only &

[mysqld]read-only