mysql主从

[root@chouyu ~]# systemctl start mariadb
[root@chouyu ~]# mysql -uroot -p

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
//创建同步账号

MariaDB [(none)]> grant replication slave on *.* to ‘xixi‘@‘192.168.220.20‘ identified by ‘xixi123‘ ;
Query OK, 0 rows affected (0.000 sec)

[root@chouyu ~]# mysql -uroot -p
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

//登录测试
[root@chouyu ~]# mysql -uxixi -pxixi123 -h192.168.220.10
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

#配置主库数据库
[root@chouyu ~]# vim /etc/my.cnf
...
[mysqld]
server-id = 10  # 添加启用binlog日志
log-bin = mysql_bin  #添加据库服务器唯一标识符，主库的server-id值必须比从库的小
#重启mysql
[root@localhost ~]# service mysqld start
Starting MySQL. SUCCESS!
 
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> 

//配置主从
[root@chouyu ~]# vim /etc/my.cnf
...
[mysqld]
server-id = 20
relay-log = mysql_relay_bin
[root@chouyu ~]# systemctl restart mariadb

//配置从数据库
MariaDB [(none)]> change master to
    -> master_host=‘192.168.220.10‘,
    -> master_user=‘xixi‘,
    -> master_password=‘xixi123‘,
    -> master_log_file=‘mysql_bin.000001‘,
    -> master_log_pos=328;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> 

[root@chouyu ~]# systemctl restart mariadb

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.220.10
                   Master_User: xixi
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql_bin.000002
           Read_Master_Log_Pos: 328
                Relay_Log_File: mysql_relay_bin.000006
                 Relay_Log_Pos: 627
         Relay_Master_Log_File: mysql_bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

重启。再查看服务器状态

//往主库写入
MariaDB [(none)]> create database xixi;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use xixi;
Database changed
MariaDB [xixi]> create table test(id int not null auto_increment,name varchar(20),age tinyint,primary key(id));
Query OK, 0 rows affected (0.003 sec)

MariaDB [xixi]> insert test(name,age) values(‘z‘,34),(‘x‘,24),(‘c‘,25);
Query OK, 3 rows affected (0.001 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [xixi]> select * from test;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | z    |   34 |
|  2 | x    |   24 |
|  3 | c    |   25 |
+----+------+------+
3 rows in set (0.000 sec)

MariaDB [xixi]> 

//去从库看一下数据同步没有
MariaDB [(none)]> select * from xixi.test;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | z    |   34 |
|  2 | x    |   24 |
|  3 | c    |   25 |
+----+------+------+
3 rows in set (0.000 sec)

MariaDB [(none)]> 

 搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

主数据库：IP192.168.220.10  有数据

从数据库：IP192.168.220.20  无数据

//查看主库数据

[root@chouyu ~]# mysql -uroot -p -e ‘show databases;‘
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| xixi               |
+--------------------+


[root@chouyu ~]# mysql -uroot -p -e ‘select * from xixi.test;‘
Enter password: 
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | z    |   34 |
|  2 | x    |   24 |
|  3 | c    |   25 |
+----+------+------+
[root@chouyu ~]# 

//从库上没数据
[root@chouyu ~]# mysql -uroot -p -e ‘show databases;‘
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
[root@chouyu ~]# 

//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
MariaDB [(none)]>  FLUSH TABLES  WITH READ LOCK;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> 

[root@chouyu ~]# mysqldump -uroot -p --all-databases > all_$(date +%F_%T).sql
Enter password: 
[root@chouyu ~]# 

[root@chouyu ~]# ls
all_2021-01-03_08:54:50.sql  all_2021-01-03_08:55:27.sql  anaconda-ks.cfg
[root@chouyu ~]# 

//将备份数据推送至从服务器
[root@chouyu ~]# scp /root/all_2021-01-03_08\:55\:27.sql ‘root@192.168.220.20:/root/‘
root@192.168.220.20‘s password: 
all_2021-01-03_08\:55\:27.sql                                       100%  500KB  10.2MB/s   00:00

[root@chouyu ~]# mysql -uroot -p <all_2021-01-03_08\:55\:27.sql
Enter password:
[root@chouyu ~]# mysql -uroot -p -e ‘show databases;‘
[root@chouyu ~]#  mysql -uroot -p123123123 -e ‘show databases;‘
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| xixi               |
+--------------------+
[root@chouyu ~]# 
[root@chouyu ~]# mysql -uroot -p123123123 -e ‘select * from xixi.test;‘
Enter password: 
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | z    |   34 |
|  2 | x    |   24 |
|  3 | c    |   25 |
+----+------+------+
[root@chouyu ~]# 

mysql主从

标签：sele   配置文件   warnings   mysqld   导致   iad   schema   form   数据推送