1:安装命令 2台机分别安装
yum –y install mysql-server
2:配置登陆用户的密码root
/usr/bin/mysqladmin -u root password 'root'
3:配置允许第三方机器访问本机Mysql,分别在2个机子上配置user表
3.1远程的的数据库 user表 ,其中host :% 代表所有的ip都可以连接,如果需要指定的ip连接,host:指定的ip
| host | user | password |
+------+------+-------------------------------------------+
| % | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+------+------+-------------------------------------------+
3.2表结构改成这样后:需要刷新权限
mysql> flush privileges;
3.3 远程的机器连接测试
[root@fei ~]# mysql -u root -p -h 连接的MySQL服务器所在的电脑IP
[root@fei ~]# mysql -u root -p -h 192.168.24.25
4.配置主从机器分别配置
1)vim /etc/f
添加
[mysqld]
server-id=1 //不同的mysql服务器id要不同,比如我另一台mysql服务器server-id=2
log-bin=mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
2)分别重新启动主从二台mysql服务
service mysqld restart
3)查看MySQL有没有变化
mysql> show VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
4)获取文件名File192.168.163.130服务器
mysql> show master status;
+------------------+----------+--------------+------------------+
| File| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|mysql-bin.000002 | 106| | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
文件名为mysql-bin.000002,106代表日志从106行以后开始
5)配置从服务器(可以实现主服务更新数据,从服务器也更新)
change master to
master_host='192.168.163.130',//mysql主服务器地址
master_user='root',
master_password='root',
master_log_file='mysql-bin.000002',
master_log_pos=106;
执行
在192.168.163.131从服务器执行
mysql> change master to
-> master_host='192.168.163.130',
-> master_user='root',
-> master_password='root',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=106;
Query OK, 0 rows affected (0.04 sec)
开启从服务器:mysql> start slave;
查询从服务器状态:mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.163.130 //主服务器地址
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes // 表示去访问主服务器,yes表示通了
Slave_SQL_Running: Yes //发送一个sql语句给主服务器,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: 106
Relay_Log_Space: 407
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:
1 row in set (0.00 sec)
6)主服务器测试,添加表数据,看下从库是是否更新表
添加表数据前:
mysql> show databases;
+--------------------+
| Database|
+--------------------+
| information_schema |
| mysql |
| test |
执行:
mysql> create database test1;
mysql> use test1;
mysql> create table user(id int(2),name char(20));
mysql> insert into user(1,'xia');
添加表数据后:
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | xia |
+------+------+
1 row in set (0.00 sec)
7)从服务测试
mysql> show databases;
+--------------------+
| Database|
+--------------------+
| information_schema |
| mysql |
| test |
| test1 |
+--------------------+
4 rows in set (0.00 sec)
mysql> use test1;
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| user|
+-----------------+
1 row in set (0.00 sec)
8)双主结构(刚刚的配置是在从服务器上配置,可以实现主服务更新数据,从服务器也更新,现在反过来,在主服务配置,实现从服务器更新,主服务器也更新)
配置从服务器,
change master to
master_host='192.168.163.131',//mysql主服务器地址
master_user='root',
master_password='root',
master_log_file='mysql-bin.000002',
master_log_pos=106;
执行
在192.168.163.130主服务器执行
mysql> change master to
-> master_host='192.168.163.131',
-> master_user='root',
-> master_password='root',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=106;
Query OK, 0 rows affected (0.02 sec)
开启从服务器:mysql> start slave;
查询从服务器状态:mysql> show slave status \G;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.163.131
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes