mysql主从数据库,分库分表等笔记

jopen 11年前

1、mysql的目录:在rpm或者yum安装时:/var/lib/mysql  在编译安装时默认目录:/usr/local/mysql

2、用rpm包安装的MySQL是不会安装/etc/my.cnf文件的,
至于为什么没有这个文件而MySQL却也能正常启动和作用,在点有两个说法,
第一种说法,my.cnf只是MySQL启动时的一个参数文件,可以没有它,这时MySQL会用内置的默认参数启动,
第二种说法,MySQL在启动时自动使用/usr/share/mysql目录下的my-medium.cnf文件,这种说法仅限于rpm包安装的MySQL,
解决方法,只需要复制一个/usr/share/mysql目录下的.cnf文件到/etc目录,并改名为my.cnf即可。

在/usr/share/mysql目录下有很多语言目录和mysql多个适用于不同类型项目的对应配置文件

3、配置mysql的字符集设置:

      在配置文件中

      在socket          = /var/lib/mysql/mysql.sock后添加default-character-set = utf8 (决定了客服端字符集和连接字符集)

      在myisam_sort_buffer_size = 8M后添加character-set-server = utf8 (决定了服务器字符集和数据库[数据库,表,字段]字符集,由于继承关系),添加collaction-server=usf8_general_ci (这是设置校验字符集,作用于排序有关order by)

4、开启mysql的log-bin:

    配置文件中开启 log-bin=mysql-bin (等于后面的内容可以随便写,mysql-bin只是说明log-bin的内容写入mysql-bin里面而已),重启mysql后,在/var/lib /mysql下能发现 mysql-bin.000001和mysql-bin.index,说明mysql-bin已经开启

5、关于mysqlbin的一些命令

     mysql提示符下查看bin-log日志
    show master status;
    #显示最后一个bin-log日志和它的最后一个位置
    show master logs;
    #查看所有的bin-log日志,同时标记最后一个pos位置

6、使用mysqlbinlog命令来查看binlog日志

     如果提示字符集错误---mysqlbinlog: unknown variable 'default-character-set=utf8' ,可以mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001来忽略字符集错误

7、什么时候会产生一个新bin-log日志
     1)重启mysql服务
     2)flush logs;
      #刷新bin-log日志,会产生一个新的bin-log日志 如:mysql-bin.00008
     3) reset master;
   #清空所有在bin-log日志,最后只剩下mysql-bin.00001 且位置为初始位置

   需求:公司内真正数据备份与恢复,9:30的时候备份数据,同时刷新bin-log日志,但是10:00数据损坏,如何去恢复9:30以来所有的数据?(在编译安装mysql环境下)

  1.9:30备份数据:
      /usr/local/mysql/bin/mysqldump -uroot -p123 test -l -F >/tmp/test.sql
     -l 将表锁了(防止在导表的过程中,又有用户修改或者添加数据),只读  -F (flush) 刷新bin-log日志(产生新的日志文件,继续保存后面的日志记录)
   2.9:30-10:00数据都会写到bin-log
     /usr/loca/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000003
    #只保存增、删、改的语句
  3.10:00数据损坏,如何恢复数据:
    1)恢复9:30备份的数据
        /usr/local/mysql/bin/mysql -uroot -p123 test </tmp/test.sql
    2)恢复9:30-10:00之间的bin-log日志中的增、删、改语句
       /usr/local/mysql/bin/mysqlbinlog --no-defaults --stop-position=367 mysql-bin.000003|/usr/local/mysql/bin/mysql -uroot -p123 test

      --stop-position 为要恢复数据在logbin的最后日志,可以通过查看日志文件查找位置,还有--start-position表示恢复从该位置以后的数据
  4.这样,数据全部恢复完毕

8、主从数据库

   从mysql服务器:
  1.vi /etc/my.cnf
  server-id = 2
  log-bin=mysql-bin
  master-host=192.168.255.1
  master-user=user1
  master-password=123
  master-port=3306
  2.重启mysql
  3.show slave status\G
  Slave_IO_Running: Yes
  #从主服务器复制bin-log成功
  Slave_SQL_Running: Yes
  #把复制过来的sql语句执行成功

4.出错
#原因,与主服务器的最后一个pos位置对接不成功
slave stop;
change master to
master_host="192.168.10.1",
master_user="user1",
master_password='123',
master_port=3306,
#在主服务器上show master status;
master_log_file="mysql-bin.000001",
master_log_pos= 279;
slave start;