mysql多实例-主从复制安装

2023-08-24 17:24:16

安装环境:Centos6.5

mysql版本：mysql-5.5.32.tar.gz

一：安装前准备：

1.安装一些依赖库

yum install cmake gcc gcc-c++ ncurses-devel bison perl zilb-devel libio-devel perl-devel -y

2.创建mysql用户与用户组

[root@oracle ~]# groupadd mysql

[root@oracle ~]# useradd -g mysql mysql

[root@oracle ~]# id mysql

uid=(mysql) gid=(mysql) 组=(mysql)

3.创建mysql数据存放目录，设定安装两个实例3306,3307

[root@oracle ~]# mkdir -p /data/{,}/data

[root@oracle ~]# tree /data

/data

├──

│   └── data

└──

    └── data

4.编译安装

tar zxvf mysql-5.5.32.tar.gz 
cd mysql-5.5.

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DDEFAULT-CHARSET=utf8 -DMYSQL_USER=mysql

make

make install

5.分别复制my.cnf配置文件和mysql启动脚本到/data/3306和3307下

[root@oracle support-files]# cp my-huge.cnf /data//my.cnf

[root@oracle support-files]# cp my-huge.cnf /data//my.cnf

[root@oracle support-files]# cp mysql.server /data//mysql

[root@oracle support-files]# cp mysql.server /data//mysql

6.修改/data目录属主

[root@oracle ~]# chown -R mysql:mysql /data

7.修改3306,3307实例的配置文件

以3306实例为例，3307修改相应位置内容
# The following options will be passed to all MySQL clients

[client]

#password       = your_password

port            =

socket          = /data//mysql.sock

# The MySQL server

[mysqld]

port            = 3306

socket          = /data/3306/mysql.sock

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 1M

table_open_cache =

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size =

query_cache_size = 32M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 

datadir=/data/3306/data
 
.......略过一段..........

log-bin=mysql-bin #开启二进制日志 ,从服务器不需要开启二进制日志，3307实例注释掉这行

server-id = 1 #实例的server-id不能相同，可以设置3307的server-id = 2

my.cnf文件末尾添加 [mysqld_safe]段，定义每个实例的错误日志文件，pid进程位置等

[mysqld_safe]

log-error=/data/3306/3306.err
pid-file=/data/3306/mysqld.pid

8，修改3306,3307实例的启动脚本文件内容

1.找到basedir=和datadir=两行，在后面加上mysql的安装目录路径和数据存放路径
basedir=/usr/local/mysql

datadir=/data/3306/data

2.定位到283行：

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

改为:

$bindir/mysqld_safe --datadir="$datadir/my.cnf" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

3307实例也修改对应位置

9.给3306,3307的启动脚本执行权限

[root@oracle ]# chmod  mysql

[root@oracle ]# chmod  mysql

10.初始化数据库

[root@oracle ~]# cd /usr/local/mysql/scripts/

[root@oracle scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data//data

[root@oracle scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data//data
到数据存放目录查看一下

[root@oracle ~]# cd /data/3306/data/ #同样查看3307
[root@oracle data]# ls
mysql performance_schema test
[root@oracle data]#

11.启动mysql

启动3306实例

[root@oracle ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &
[1] 6083

查看一下
[root@oracle ~]# netstat -tunlp | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6372/mysqld
启动3307实例

[root@oracle ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &
[2] 6393
[root@oracle ~]# netstat -tnlp | grep mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 6670/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6372/mysqld
启动成功。

再看一下/data目录下

12.给数据库设置密码提示错误 -bash: mysqladmin: command not found

[root@oracle ]# mysqladmin

-bash: mysqladmin: command not found

[root@oracle ]#

执行指令系统默认是到 /usr/sbin 下寻找调用指令的，将/usr/local/mysql/bin/ 下的几个常用管理命令创建软连接到/usr/sbin下，以后使用mysql指令就不需要用绝对路径了

[root@oracle ]# ln -s /usr/local/mysql/bin/mysqladmin /usr/sbin/

[root@oracle ]# ln -s /usr/local/mysql/bin/mysqld_safe /usr/sbin/
[root@oracle 3306]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/

设置密码

[root@oracle ]# mysqladmin -u root password root123 -s /data//mysql.scok

[root@oracle ]# mysqladmin -u root password root123 -s /data//mysql.scok

登录数据库出现错误，登录被拒绝。

[root@oracle ]# mysql -uroot -p -S /data//mysql.sock

Enter password:

ERROR  (): Access denied for user 'root'@'localhost' (using password: YES)
登录被拒绝...这个让我郁闷了一会...
先跳过权限表登录，再修改密码

mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-table &

再登录

mysql -uroot -S /data/3306/mysql.sock

[root@oracle 3306]# mysql -uroot -S /data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-log Source distribution

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.

（郁闷：前面设置的密码这里查询怎么是为空呢。。。。）

先删除user名称为空的用户

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delete from user where user='';
Query OK, 2 rows affected (0.03 sec)

修改密码

mysql> update user set password=PASSWORD('root123456') where user = 'root';
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4 Changed: 4 Warnings: 0

退出后重启一下服务，登录成功（几乎每次刚安装好后登录都会遇到这个问题。。。）

这里总结一下多实例的启动，关闭，登录的指令：
启动指令：mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &
关闭指令：mysqladmin -S /data/3306/mysql.sock shutdown （有密码要指定-u ， -p选项）
登录指令：mysql -uroot -p -S /data/3306/mysql.sock
指定实例登录跳过权限表：
跳过权限：mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables
跳过权限登录：mysql -uroot  -S /data/3306/mysql.sock

//////////////////////////////////////***************************//////////////////////////////////////////////////////////////////////////

同理，3307实例也遇到了登录问题，使用同样的方式解决即可。下面开始进行主从复制配置。

主库端的配置：

1.主库中添加一个用于主从之间通信的用户并授权

mysql> grant file on *.* to 'backup'@'192.168.%.%' identified by 'root123456';

mysql> grant replication slave on *.* to 'backup'@'192.168.%.%' identified by 'root123456';

2.检查一下主库二进制是否开启了

mysql> show variables like 'log_bin';

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

| Variable_name | Value |

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

| log_bin       | ON    |

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

 row in set (0.01 sec)
再查看当前二进制记录信息

从库端配置

在从库执行master change to 配置主从库连接参数（忘记指令可以在mysql中执行help master change to查看）
mysql> use mysql;

mysql> change master to \

-> master_host='192.168.214.133'\
-> master_user='backup'\
-> master_password='root123456'\
-> master_log_file='mysql-bin.000004'\
-> master_log_pos=410;

启动从库slave

mysql> start slave;

Query OK,  rows affected (0.03 sec)

查看slave是不是启动正常了

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.214.133
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 410
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
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: 410
Relay_Log_Space: 410
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)

接下来，在主库中创建一个数据库，测试主从复制功能。

mysql> show tables;

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

| Tables_in_demo |

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

| test           |

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

 row in set (0.00 sec)

mysql> desc test;

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

| Field | Type        | Null | Key | Default | Extra |

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

| id    | varchar() | YES  |     | NULL    |       |

| name  | varchar() | YES  |     | NULL    |       |

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

 rows in set (0.00 sec)

mysql> insert into test values ('','testname');

Query OK,  row affected (0.36 sec)

mysql> select * from test;

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

| id   | name     |

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

|   | testname |

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

 row in set (0.44 sec)

mysql>

再看从库同步的信息

mysql> show databases;

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

| Database           |

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

| information_schema |

| demo               |

| mysql              |

| performance_schema |

| test               |

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

 rows in set (0.00 sec)

mysql> select * from demo.test;

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

| id   | name     |

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

|   | testname |

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

 row in set (0.01 sec)

可以看到主从库信息同步也正常。

在有些情况下，从库不需要同步主库的所有数据库，有时只需要同步主库里的指定的数据库，那么在从库的my.cnf中添加 replicate-do-db=dbname 指定要同步的库名称即可。

这个问题可以参考 http://blog.csdn.net/goustzhu/article/details/9339621 文章。

或者 http://bbs.chinaunix.net/thread-1187327-1-1.html

码农公寓

相关文章