mysql强制读主库命令

mysql强制读主库命令

1、(主库)修改/etc/myf配置文件

innodb_strict_mode=on

#innodb 共享表空间设置

innodb_file_per_table = 1

innodb_data_home_dir = /var/lib/mysql

innodb_data_file_path = ibdata1:6500M:autoextend

#innodb 日志相关

innodb_log_file_size = 500M

innodb_log_buffer_size = 8M

innodb_log_files_in_group = 4

innodb_log_group_home_dir=/var/lib/mysql

innodb_flush_log_at_trx_commit=1

#innodb 内存相关参数

innodb_buffer_pool_size=2048M

innodb_additional_mem_pool_size = 32M

innodb_thread_concurrency =0

innodb_flush_method = O_DIRECT

expire_logs_days    = 5

log_bin             = /var/lib/mysql/mysql-bin

log_bin_index       = /var/lib/mysql/mysql-bin.index

2、(从库)修改/etc/myf配置文件

port          = 3306

socket        = /var/lib/mysql/mysql.sock

skip-external-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

binlog_format=mixed

server-id=50          //从库注意一定要修改此参数

lower_case_table_names=1

log-slow-queries = /tmp/mysql-slow.log

long_query_time = 2

max_connections=500

binlog-ignore-db=mysql #被忽略的数据库

#innodb 共享表空间设置

innodb_file_per_table = 1

innodb_data_home_dir = /var/lib/mysql

innodb_data_file_path = ibdata1:6500M:autoextend

#innodb 日志相关

innodb_log_file_size = 500M

innodb_log_buffer_size = 8M

innodb_log_files_in_group = 4

innodb_log_group_home_dir=/var/lib/mysql

innodb_flush_log_at_trx_commit=1

#innodb 内存相关参数

innodb_buffer_pool_size=2048M

innodb_additional_mem_pool_size = 32M

innodb_thread_concurrency =0

innodb_flush_method = O_DIRECT

log_slave_updates=1

expire_logs_days    = 5

log_bin             = /var/lib/mysql/mysql-bin

log_bin_index       = /var/lib/mysql/mysql-bin.index

relay_log           = /var/lib/mysql/replay-bin

relay_log_index     = /var/lib/mysql/replay-bin.index

3、修改主库innodb日志大小

[root@dsdb-1 mysql]# ls ib_logfile*

ib_logfile0  ib_logfile1

server mysql stop

mv ib_logfile*  /root

server mysql start

4、(主库)拷贝主库文件到备库

由于主库是已经运行很久的一个库，数据量在100G以上，在综合考虑mysqldump 备份恢复需要很久的时间，采用直接拷贝文件到备库的方式建立slave

由于主库包含InnoDB表的拷贝：4.1在master主机上使用mysql命令行客户端，并阻塞所有的写入操作

mysql> FLUSH TABLES WITH READ LOCK;

4.2记录偏移量值

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000056 |      107 |              |                  |

+------------------+----------+--------------+------------------

4.3、在另外一个shell客户端关闭master数据库

mysqladmin shudown -p

4.4、拷贝并数据文件

mysqldump --all-databases --master-data > /tmp/datadump.db

或者如果数据量比较大的话，可以用直接拷贝目录的方式，也省去了执行insert语句更新索引的开销。

tar czvf /tmp/ /var/lib/mysql/

4.5 重启mysql master服务

Service mysql start

4.6在master上释放读锁(如果前面执行过FLUSH TABLES WITH READ LOCK;)

mysql> UNLOCK TABLES;

4.7 主库传输文件到备库

Scp  /tmp/  10.10.12.50：/tmp

在master上设置复制用户及密码

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.10.12.%' IDENTIFIED BY '123456';

如果主库不包含InnoDB表的拷贝，拷贝方式可以考虑，不用停机：    1 在master主机上使用mysql命令行客户端，将所有数据刷进磁盘，并阻塞所有的写入操作 mysql> FLUSH TABLES WITH READ LOCK;       2 拷贝并压缩数据文件 # tar czvf /tmp/ /usr/local/mysql/data       3 释放读锁 mysql> UNLOCK TABLES;   5、在从机上设置复制  5.1 设置主库，从库的配置文件myf  (1) 需要设置解压出来文件的权限确保slave能够读取或更改它们 Mv mysql mysql_bak Mv /tmp/  /var/lib/ tar xzvf   mv   /var/lib/var/lib/mysql  /var/lib/ (2) 删除data目录下的autof文件及err日志 rm -rf  dsdb-1.pid rm -rf  dsdb-1-relay-bin.000021 rm -rf  relay-log.info rm -rf  dsdb-1-relay-bin.000022 rm -rf  dsdb-1-relay-bin.000023 rm -rf  dsdb-1-relay-bin.000024 rm -rf  dsdb-1-relay-bin.000025 rm -rf  dsdb-1-relay-bin.000026 rm -rf  dsdb-1-relay-bin.000027 rm -rf  dsdb-1-relay-bin.000028 rm -rf  dsdb-1-relay-bin.000029 rm -rf  dsdb-1-relay-bin.000030 rm -rf  dsdb-1-relay-bin.000031 rm -rf  dsdb-1-relay-bin.000032 rm -rf  dsdb-1-relay-bin.000033 rm -rf  dsdb-1-relay-bin.index rm -rf  dsdb-1-relay-bin.000034 rm -rf  mysql-bin.000052 rm -rf  mysql-bin.index rm -rf  master.info rm -rf   rm -rf  mysql-bin.000053 5.2 启动mysql服务 Service mysql start 如果在master上加读锁(如果前面执行过FLUSH TABLES WITH READ LOCK;)从库上执行 mysql> UNLOCK TABLES; mysql> stop SLAVE;  5.3 从库上执行change master操作 mysql> CHANGE MASTER TO master_host='10.10.12.49', master_port=3306, master_user='repl',master_password='123456', master_log_file='mysql-bin.000056', master_log_pos=107; ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> reset slave; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO master_host='10.10.12.49', master_port=3306, master_user='repl',master_password='123456', master_log_file='mysql-bin.000056', master_log_pos=107; Query OK, 0 rows affected (0.02 sec) 8.4 从库启动复制进程 mysql> START SLAVE;  9、(从库)检查同步情况 show slave status G; 验证： 主库： mysql> create database t; Query OK, 1 row affected (0.03 sec) mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | t                  | | test               | +--------------------+ 5 rows in set (0.00 sec) mysql> use t; Database changed mysql> create table t(id int,a varchar(10)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED      -> ; Query OK, 0 rows affected (0.24 sec) mysql> insert into t values(1,'a'); Query OK, 1 row affected (0.05 sec) mysql> insert into t values(2,'a'); Query OK, 1 row affected (0.07 sec) mysql> insert into t values(3,'a'); Query OK, 1 row affected (0.05 sec) 从库 查看： mysql> select * From t; +------+------+ | id   | a    | +------+------+ |    1 | a    | |    2 | a    | |    3 | a    | +------+------+ 3 rows in set (0.00 sec) 至此主从master-slave已经建立起来了