linux mysql 主从安装_LINUX 下mysql主从安装与同步

两台服务器

主：172.16.0.120    Master120

从：172.16.0.121      backup121

两台都要安装mysql如下步骤

安装系统所需要的依赖包

[root@ Master120 ~]# yum -y install gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel*  cmake  bison

创建mysql用户与用户组

[root@ Master120 ~]# groupadd mysql

[root@ Master120 ~]# useradd -r -g mysql mysql

安装mysql

[root@ Master120 ~]# tar zxvf mysql-5.5.25.tar.gz

[root@ Master120 ~]# cd mysql-5.5.25

[root@Master120 mysql-5.5.25]# cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql/ -DMYSQL_DATADIR=/opt/mysql/data -DMYSQL_UNIX_ADDR=/opt/mysql/data/mysqld.sock -DWITH_INNOBASE_STORAGE_ENGINE=1  -DENABLED_LOCAL_INFILE=1 -DMYSQL_TCP_PORT=3306 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DMYSQL_UNIX_ADDR=/opt/mysql/data/mysql.sock -DMYSQL_USER=mysql  -DWITH_DEBUG=0

[root@ Master120 mysql-5.5.25]# make && make install

赋予相关权限

[root@ Master120 mysql-5.5.25]# chown -R mysql:mysql /opt/mysql

[root@Master120 mysql-5.5.25]#  /opt/mysql/scripts/mysql_install_db --user=mysql --basedir=/opt/mysql   --datadir=/opt/mysql/data

mysql配置文件

[root@ Master120 mysql-5.5.25]# cp /opt/mysql/support-files/my-large.cnf /etc/my.cnf

[root@Master120 mysql-5.5.25]# cp /opt/mysql/support-files/mysql.server /etc/init.d/mysql

[root@Master120 mysql-5.5.25]# chmod +x /etc/init.d/mysql

增加自动启动

[root@Master120 mysql-5.5.25]# chkconfig mysql on

启动mysql

[root@Master120 mysql-5.5.25]# /etc/init.d/mysql start

Starting MySQL......                                       [  OK  ]

查看mysql的3306端口，看到下面说明启动成功

[root@Master120 mysql-5.5.25]#ps aux | grep 3306

mysql    17237  3.6  8.4 615660 86264 pts/2    Sl   22:10   0:01 /opt/mysql/bin/mysqld --basedir=/opt/mysql --datadir=/opt/mysql/data --plugin-dir=/opt/mysql/lib/plugin --user=mysql --log-error=/opt/mysql/data/Master120.err --pid-file=/opt/mysql/data/Master120.pid --socket=/opt/mysql/data/mysql.sock --port=3306

root     17798  0.0  0.0  61228   784 pts/2    S+   22:10   0:00 grep 3306

设置mysql初始密码，123456是密码，你可以设置自己需要的密码

[root@Master120 mysql-5.5.25]# /opt/mysql/bin/mysqladmin -u root password '123456'

[root@Master120 mysql-5.5.25]# /opt/mysql/bin/mysql -uroot -p123456

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.25-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 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>

主从复制的配置

主的my.cnf的server-id=1，每个同步服务都必须设定一个唯一的编号。

在Master(这里为Master120机器)上增加一个用于复制的账号：

mysql>GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'172.16.0.%'  IDENTIFIED BY '123456';

mysql>FLUSH REPLICATION;

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |    245 |              |                  |

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

1 row in set (0.00 sec)

设置Slave主机

修改my.cnf的server-id,内容如下：

backup121主机

server-id=2

开启Master与Slave的同步

在Slave上执行如下命令

mysql>CHANGE MASTER TO MASTER_HOST='172.16.0.120',

->MASTER_USER='repl',

->MASTER_PASSWORD='123456',

->MASTER_LOG_FILE='mysql-bin.000001',

->MASTER_LOG_POS=245;

之后执行

mysql>slave start;

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.0.120

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 245

Relay_Log_File: backup121-relay-bin.000001

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:

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: 245

Relay_Log_Space: 556

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.00 sec)

看到都为yes，说明成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

注意防火墙，关闭防火墙或者增加3306端口对外访问

如果在生产库中从库同步出错我们可以在从库的my.cnf增加Slave_skip_errors =all跳过全部错误。