mysql主从

说明安装环境：ubuntu10/11，mysql 5.1

一，mysql安装

1，查看mysql是否安装

sudo aptitude show mysql-server

sudo aptitude show mysql-client

 

2，安装

 sudo apt-get install [--fix-missing] mysql-server mysql-client

信息如下：

The following extra packages will be installed:
  libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient16 libnet-daemon-perl libplrpc-perl mysql-client-5.1 mysql-client-core-5.1 mysql-common
  mysql-server-5.1 mysql-server-core-5.1

 

Suggested packages:
  libipc-sharedcache-perl tinyca mailx
The following NEW packages will be installed:
  libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient16 libnet-daemon-perl libplrpc-perl mysql-client mysql-client-5.1 mysql-client-core-5.1
  mysql-common mysql-server mysql-server-5.1 mysql-server-core-5.1

设置root密码

 

3，配置zqm@ubuntu:~$ sudo vim /etc/mysql/my.cnf

注释掉

#bind-address           = 127.0.0.1

修改如下

[client]

default-character-set=utf8

[mysqld]

datadir=/data/mysql

default-character-set=utf8
init_connect='SET NAMES UTF8'

 

 

如果修改了datadir，就要相应在文件

/etc/apparmor.d/usr.sbin.mysqld中添加

/data/mysql/ r,
/data/mysql/** rwk,

让修改生效:sudo invoke-rc.d apparmor restart

之后复制原来mysql数据目录的数据到新目录

sudo cp -r /var/lib/mysql /data/

修改属主

sudo chown -R mysql:mysql /data/mysql

 

启动mysql

sudo service mysql start

 

 

 

二，mysql主从

1，授权

GRANT REPLICATION SLAVE ON *.* TO 'slave001'@'192.168.131.129' identified by "slavepass";

2，修改主配置

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 7
max_binlog_size         = 100M
binlog_do_db            = dbname

3，记录主库状态

show master status;

show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 | zqm         |                  |
+------------------+----------+--------------+------------------+

 

4,配置从库

sudo vim /etc/mysql/my.cnf

server-id=2

 

5，重启从库

sudo service mysql restart

 

6，登陆mysql，执行chang master

mysql -uroot -p

执行：

change master to master_host='192.168.131.128',master_user='slave001',master_password='slavepass',master_log_file='mysql-bin.000001',master_log_pos=106;

 

 正确执行后

start slave

 

7，查看slave状态

show slave status \G;

 

备注，如果发现chang mater设置错了master的ip，可以先stop slave，之后再chang master，再start slave

 

三，主从复制中的错误处理

1，主键重复插入错误

 Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '6' for key 'PRIMARY'' on query. Default database: 'fabu'. Query: 'insert into t values (6,'f')'

 

处理方案：跳过此条插入sql

slave stop;

set global SQL_slave_SKIP_COUNTER=1;

slave start;

 

 

四. 查看mysql加那个配置文件

 

1.ps -ef |grep mysql  不行就用2的方法

2. strace -eopen mysqld