mysql 热备份

原创 2015年07月07日 10:46:46

获取版本号

 mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)


删除mysql

1.sudo apt-get autoremove --purge mysql-server-5.5

2.sudo apt-get remove mysql-common

清理残留数据

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

重新安装sql

去掉地址绑定

nano /etc/mysql/my.cnf    #编辑配置文件
找到bind-address = 127.0.0.1
改为
#bind-address = 127.0.0.1

mysql> create database aaa;

mysql> use aaa;
mysql> create table `mytesttable` ( name varchar(20));
mysql> create table bbb(name varchar(30));
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| bbb           |
| mytesttable   |
+---------------+
2 rows in set (0.00 sec)

mysql>quit

a@ubuntu:~$ sudo netstat -tap | grep mysql

a@ubuntu:/var/log$ sudo /etc/init.d/mysql restart

C:\Documents and Settings\Administrator>mysql -usync -p12345 -h192.168.1.110
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 72
Server version: 5.5.43-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>


a@ubuntu:~$ sudo /etc/init.d/mysql start

 * Starting MySQL database server mysqld                                 [fail]

error.log

InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
ulimit -s unlimited
重启了虚拟机

ENGINE选项

InnoDB 带行锁定和外键的事务安全表

MyISAM二进制轻便式存储引擎,此引擎是MySQL所用的默认存储引擎

mysql> create table tab1(id int(10),name varchar(20),phone varchar(20));

mysql> insert into tab1 values(1,'wangjinrong','10086');

mysql> select * from tab1;
+------+-------------+-------+
| id   | name        | phone |
+------+-------------+-------+
|    1 | wangjinrong | 10086 |
+------+-------------+-------+
1 row in set (0.00 sec)

重置master

mysql> reset master;
Query OK, 0 rows affected (0.05 sec)

mysql> exit

备份数据库

a@ubuntu:~$ mysqldump -uroot -p1 aaa -l -F > /tmp/test.sql;
a@ubuntu:~$ ls /tmp

a@ubuntu:~$ scp /tmp/test.sql 192.168.1.113:/tmp/
ssh: connect to host 192.168.1.113 port 22: Connection refused
lost connection

首先在本机执行:sudo apt-get install ssh

a@ubuntu:~$ scp /tmp/test.sql 192.168.1.113:/tmp/
The authenticity of host '192.168.1.113 (192.168.1.113)' can't be established.
ECDSA key fingerprint is 95:d3:68:6f:a5:87:26:86:29:cf:50:ca:66:a3:15:5f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.113' (ECDSA) to the list of known hosts.
a@192.168.1.113's password: 1
test.sql                                      100% 1897     1.9KB/s   00:00    

创建备份角色

mysql>   grant replication slave on *.* to 'sync0'@'%' identified by '12345'
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user1@192.168.1.113 \G;
*************************** 1. row ***************************
Grants for user1@192.168.1.113: GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.1.113' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9'
1 row in set (0.00 sec)
mysql> show grants for sync\G;
*************************** 1. row ***************************
Grants for sync@%: GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9'
1 row in set (0.00 sec)

从机器
mysql> reset master;

ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER

恢复数据库

a@ubuntu:/tmp$ mysql -uroot -p1 aaa -v -f < /tmp/test.sql;

a@ubuntu:/tmp$ sudo gedit /etc/mysql/my.cnf;
server-id        = 2
master-host     = 192.168.1.103
master-user     = user1
master-password = 12345
master-port     = 3306
a@ubuntu:/tmp$ sudo /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld                                 [ OK ]
 * Starting MySQL database server mysqld                                 [fail]

查看日志

a@ubuntu:/tmp$ gedit /var/log/mysql/error.log

150707 20:48:37 InnoDB: 5.5.43 started; log sequence number 1599144
150707 20:48:37 [ERROR] /usr/sbin/mysqld: unknown variable 'master-host=192.168.1.103'
150707 20:48:37 [ERROR] Aborting
#master-host     = 192.168.1.103
#master-user     = user1
#master-password = 12345

这样可以重新启动mysql

 

主服务器配置文件修改

 [mysqld]

#bind-address        = 127.0.0.1

server-id        = 1
log_bin            = /var/log/mysql/mysql-bin.log
log_slow_queries   = /var/log/mysql/mysql-slow.log

binlog_do_db        = aaa



显示master状态

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      107 | aaa          |                  |
+------------------+----------+--------------+------------------+

从机器的设置

[mysqld]
#bind-address        = 127.0.0.1
server-id        = 2
read-only
replicate-do-db = aaa

设置master的信息

mysql> change master to MASTER_HOST='192.168.1.110', MASTER_USER='sync0',MASTER_PASSWORD='12345',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=107;

启动slave

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看slave状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.110
                  Master_User: sync0
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 249
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 395
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: aaa
          Replicate_Ignore_DB:
           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: 249
              Relay_Log_Space: 552
              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: 0
Master_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
1 row in set (0.01 sec)

相关文章推荐

mysql双击热备份

  • 2013年12月11日 13:19
  • 47KB
  • 下载

MySQL热备份

  • 2013年07月24日 15:53
  • 218KB
  • 下载

Mysql 双机双向热备份

【背景说明】           目前应用中的mysql并没有采用双机热备的策略,但考虑到系统的高可用性,双机热备份是必要的,另外实现数据热备份后还可进一步实现读写分离,提高应用中访问数据的性能,何乐...

mysql热备份

  • 2012年03月15日 16:03
  • 120KB
  • 下载

MySQL双机热备份实施方案

1、MySQL数据库没有增量备份的机制,当数据量太大的时候备份是一个很大的问题。还好MySQL数据库提供了一种主从备份的机制,其实就是把主数据库的所有的数据同时写到备份数据库中。实现MySQL数据库的...

mysql主从热备份

环境: 主机:192.168.53.81 从机:192.168.53.82   一、查看主库mysql版本号,从库的mysql版本号要和主库一致。 [root@53-81 ~]# /usr/...

MySQL双机热备份的配置及原理

MySQL双机热备份的配置及原理 1.       主题 MySQL双机热备份的配置及原理 2.       摘要 双机热备就是使用MySQL提供的一种主从备份机制实现。其实就是把主数据库(m...

MySQL之——双机双向热备份

一、说明  目前应用中的mysql并没有采用双机热备的策略,但考虑到系统的高可用性,双机热备份是必要的,另外实现数据热备份后还可进一步实现读写分离,提高应用中访问数据的性能,何乐而不为呢,目前手上有几...

mysql双机热备份的实现步骤

MySQL 提供了数据库的同步功能,这对我们实现数据库的冗灾、备份、恢复、负载均衡等都是有极大帮助的。本文描述了常见的同步设置方法。 同步有两种形式: “主-从”,和“主-主”。 一:“主-从”...

MySql 主从热备份工作原理

MySql 主从热备份工作原理 简单的说:就是主服务器上执行过的sql语句会保存在binLog里面,别的从服务器把他同步过来,然后重复执行一遍,那么它们就能一直同步啦。 我们进一步详细介绍...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql 热备份
举报原因:
原因补充:

(最多只允许输入30个字)