在linux同一台主机下面搭建两个mysql 实例并实现主从复制 (二)

  • 内容
  • 相关

Image

 根据需求现在需要测试mysql主从配置,但是由于本地主机资源有限,现在需要在一台Red Hat主机上面搭建两到三个Mysql实例,以方便测试mysql主从。

  实验分三大步骤:

  1,在Red Hat上面安装MySQL server 

  2,利用MySQL的mysqld_multi配置多个实例

  3,搭建MySQL主从

开始实验:

   2,利用MySQL的mysqld_multi配置多个实例

   首先确保上一步安装的mysql server正常运行  

   


[root@TEST ~]# mysqladmin ping
mysqld is alive
[root@TEST ~]# /etc/init.d/mysqld status
MySQL running (21075)[  OK  ]


   在同一台主机上面运行多个mysql instance的时候需要用到mysql的一个特殊功能mysqld_multi,

   在linux shell下输入 mysqld_multi  --example 就可以得到配置多个实例的一些例子

   


[root@localhost ~]# mysqld_multi --example
### there are many commons
[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = my_password
[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /var/lib/mysql2/hostname.pid2
datadir    = /var/lib/mysql2
language   = /usr/share/mysql/english
user       = unix_user1
[mysqld3]
mysqld     = /path/to/mysqld_safe
ledir      = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /var/lib/mysql3/hostname.pid3
datadir    = /var/lib/mysql3
language   = /usr/share/mysql/swedish
user       = unix_user2
[mysqld4]
socket     = /tmp/mysql.sock4
port       = 3309
pid-file   = /var/lib/mysql4/hostname.pid4
datadir    = /var/lib/mysql4
language   = /usr/share/mysql/estonia
user       = unix_user3                                                                                                                                                                                                      
[mysqld6]
socket     = /tmp/mysql.sock6
port       = 3311
pid-file   = /var/lib/mysql6/hostname.pid6
datadir    = /var/lib/mysql6
language   = /usr/share/mysql/japanese
user       = unix_user4

   此试验中需要再额外创建两个instance,所以根据上面的例子,把所需的配置信息添加到mysql的主配置文件/etc/my.cnf 后面,例如:

   


[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = root       #主要用于mysql_multi stop  2/3所用,如果没有就不能停止
password   = 222222  #主要用于mysql_multi stop 2/3所用密码,如果没有就不能停止
[mysqld2] 
socket     = /tmp/mysql.sock2   
port       = 3307
pid-file   = /var/run/mysqld/mysqld.pid2
#log-slow-queries = /var/log/mysql2/slow-query-log   ## mysql slow log
#relay-log        = /var/spool/mysqld2/mysqld-relay-bin   ## relay for replication
datadir    = /var/lib/mysql2    ## mysql data file folder
user       = mysql
[mysqld3]
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /var/run/mysqld/mysqld.pid3
#log-slow-queries = /var/log/mysql3/slow-query-log
#relay-log        = /var/spool/mysqld3/mysqld-relay-bin
datadir    = /var/lib/mysql3
user       = mysql

   接下来根据配置文件的信息创建对应的目录并设置对应的权限

   


## make mysql data file folder
mkdir -p /var/lib/mysql2
mkdir -p /var/lib/mysql3
## make log folder
mkdir -p /var/log/mysql2
mkdir -p /var/log/mysql3
mkdir -p /var/spool/mysql2
mkdir -p /var/spool/mysql3

   修改mysql的数据目录确保只有mysql用户才有访问权限


chown mysql:root /var/lib/mysql2
chown mysql:root /var/lib/mysql3

   利用 mysql_install_db 安装初始化库


mysql_install_db  --user=mysql  --datadir=/var/lib/mysql2
mysql_install_db  --user=mysql  --datadir=/var/lib/mysql3

   最关键的时刻到了,启动安装的instance

   


[root@localhost ~]# mysqld_multi --help |grep Usage
Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
[root@localhost ~]# mysqld_multi start 2
[root@localhost ~]# mysqld_multi start 3    #上面 [mysqld3]里面的数字

   尝试登陆:

   


[root@localhost ~]# mysql -S /tmp/mysql.sock2 -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71 Source distribution
Copyright (c) 2000, 2013, 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> exit
Bye
[root@localhost ~]# mysql -S /tmp/mysql.sock3 -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.71-log Source distribution
Copyright (c) 2000, 2013, 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>

   到此在同一台主机上面创建多个mysql instance的算是完成了。

   但是最后需要说明的是,第一次当我尝试stop 这些个instance的时候,使用 ”mysqld_multi stop 2" 却不起作用,网上查了下说是需要grant一个 shutdown的权限即可,  

   


mysql> show grants for root@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '08a9554c6a9d5b2a' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> grant SHUTDOWN on *.* to 'root'@'localhost' identified by '222222'; ;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for root@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '08a9554c6a9d5b2a' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> exit
Bye
[root@localhost ~]# mysqld_multi stop 3
[root@localhost ~]# mysqld_multi stop 2
[root@localhost ~]#
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock2
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock2' (2)
[root@localhost ~]#

   OK,第二步测试通过  

最后的主从复制,请参照windows下安装多个mysql服务及主从复制——备忘录(已实际验证)

 您阅读这篇文章共花了:

上一篇:在linux同一台主机下面搭建两个mysql 实例并实现主从复制 (一)

下一篇:linux下mysql的root密码忘记解决方法

本文标签:    

版权声明:本文依据CC-BY-NC-SA 3.0协议发布,若无特殊注明,本文皆为《fishyoung》原创,转载请保留文章出处。

本文链接:在linux同一台主机下面搭建两个mysql 实例并实现主从复制 (二) - http://www.fishyoung.com/post-55.html