如何在一台计算机服务器安装多个mysql数据库。

1、复制/var/local/mysql
cd /var/local
cp mysql mysql3306
cp mysql mysql3307
cp mysql mysql3308
chown mysql:mysql mysql3306
chown mysql:mysql mysql3307
chown mysql:mysql mysql3308
2、数据初始化
/usr/local/mysql/scripts/mysql_install_db –basedir=/usr/local/mysql3306 –datadir=/var/lib/mysql3306 –user=mysql
mysql3307、mysql3308同理!
3、复制配置文件
cp mysql/support-files/my-medium.cnf mysql3306/my.cnf
cp mysql/support-files/my-medium.cnf mysql3307/my.cnf
cp mysql/support-files/my-medium.cnf mysql3308/my.cnf
4、修改各个配置文件
vi mysql3306/my.cnf
[client]
port = 3306
socket = /tmp/mysql3306.sock
[mysqld]
port = 3306
sock = /tmp/mysql3306.sock
report_port = 3306
server-id = 3306
同理mysql3307、mysql3308
5、复制服务文件
cp mysql/support-files/mysql.server /etc/init.d/mysqld3306
cp mysql/support-files/mysql.server /etc/init.d/mysqld3307
cp mysql/support-files/mysql.server /etc/init.d/mysqld3308
6、修改服务文件
vi /etc/init.d/mysqld3306
basedir=/usr/local/mysql3306 程序目录
datadir=/var/lib/mysql3306 数据目录
同理mysql3307、mysql3308
7、启动服务
service mysqld3306 start
service mysqld3307 start
service mysqld3308 start
#netstat -ntl
8、链接mysql服务器
/usr/local/mysql/bin/mysql –socket=/tmp/mysql3306.sock
mysql>show variables like ‘%port%’

/usr/local/mysql/bin/mysql –socket=/tmp/mysql3306.sock -u 用户名 -p

9、设置启动级别
chkconfig –level 3 mysql3306 on
chkconfig –level 3 mysql3307 on
chkconfig –level 3 mysql3308 on

10、客户端操作
mysql -u win -P 3306 -h 192.168.161.137 -p

 

 

一主多从
主
mysql>grant file,replication slave,replication client,super on *.* to backup@’192.168.161.129′ identified by ‘linux’;
mysql>show master status;
从
vi /etc/my.cnf
[mysqld_multi]
msyqld=/usr/bin/mysqld_safe
msyqldadmin=/usr/bin/mysqladmin
user = mysql
password=mysql
[mysqld1] 1这里必须是整数
port=3306
sock=/var/lib/mysql/mysql.sock
pid-file=/tmp/mysql3306.pid
datadir=/var/lib/mysql/data1
log-bin=msyql-bin
server-id = 2
.
.
.
master-host=
master-user=
master-password=
master-port=
[msyqld2]
port=3306
sock=/var/lib/mysql/mysql3307.sock
pid-file=/tmp/mysql3307.pid
datadir=/var/lib/mysql/data2
log-bin=msyql-bin
server-id = 3
.
.
.
master-host=
master-user=
master-password=
master-port=

/usr/bin/mysql_install_db –datadir=/var/lib/mysq/data1 –user=mysql
/usr/bin/mysql_install_db –datadir=/var/lib/mysq/data2 –user=mysql
/usr/bin/mysqld_multi –defaults-extra-file=/etc/my.cnf start 1,2
mysql -S /var/lib/mysql/data1/mysql3306.sock
mysql>show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 

mysql常见错误：

Slave_IO_Running: NO

原因有:1、网络不通 2、selinux没有关 3、主mysql：log-bin=mysql-bin没有开启

解决：以上作完之后 mysql>stop slave

mysql>start slave

Slave_SQL_Running: NO

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

解决如下：

mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>reset slave;