Mysql中怎么建造数据库并配置主从

       这篇文章将为大家详细讲解有关Mysql中怎么创建数据库并配置主从，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。
 
1、主服务器上创建一个用于复制的账户。
 
     mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.101.3' IDENTIFILED BY 'Mysqlrepl';
     mysql> flush privileges;
 
2、主服务器参数修改
 
[root@localhost ~]# vi /usr/my.cnf
修改如下内容
server-id = 1
 log-bin=mysql-bin
 
3、主服务器备份数据，并传输
 
 
mysql>  flush tables with read lock;
 mysql> show master status;
 +------------------+----------+--------------+------------------+-------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
 | mysql-bin.00002 |     120 |              |                  |                   |
 +------------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)
 
 
[root@localhost ~]# mysqldump -uroot -p --all-databases | gzip > export_t.sql.gz
 [root@localhost ~]# scp export_t.sql.gz root@192.168.101.3:/tmp/
 
mysql> unlock tables;
 
4、从服务器恢复数据
 
[root@localhost bin]# gunzip export_t.sql.gz
 [root@localhost bin]# mysql -uroot -p
 mysql> source /tmp/export_t.sql.gz;
 
5、修改从服务器配置
 
[root@localhost bin]# vi /usr/my.cnf
添加如下内容
server-id = 2
重启服务器
[root@localhost ~]# service mysql restar
 
6、从服务器设置主从配置
 
 
mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.101.5',
 -> MASTER_USER='repl',  
 -> MASTER_PASSWORD='Mysqlrepl',
 -> MASTER_LOG_FILE='mysql-bin.00002',
 -> MASTER_PORT=3306,
 -> MASTER_LOG_POS=120;
 
 
 MASTER_HOST指的是主服务器的IP地址，
MASTER_USER指的是复制的账户
MASTER_PASSWORD指的是账户的密码
MASTER_PORT指的是主服务器端口
MASTER_LOG_FILE指的是bin-log的文件
MASTER_LOG_POS指的是日志文件位
 
7、从服务器启动slave线程
 
 
mysql> start slave;
 mysql> show processlist;
 +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+
 | Id | User        | Host      | db   | Command | Time | State                                                                       | Info             | Rows_sent | Rows_examined |
 +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+
 |  1 | system user |           | NULL | Connect |  714 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |         0 |             0 |
 |  2 | system user |           | NULL | Connect |  714 | Waiting for master to send event                                            | NULL             |         0 |             0 |
 |  4 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |         0 |             0 |
 +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+
 3 rows in set (0.08 sec)
 
这已经表明主从已经搭建成功。