Mysql主从复制配置

Mysql配置文件:

mv /etc/my.cnf /etc/my.cnf.bak

vi /etc/my.cnf

主库:

[mysqld]
join_buffer_size = 128M 
sort_buffer_size = 2M 
read_rnd_buffer_size = 2M 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
lower_case_table_names = 1
innodb_strict_mode = 1  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
symbolic-links=0 
character_set_server = utf8 
log-bin = mysql-bin
binlog_format=row
#####主库 从库id不能一样######
server-id = 2
#####主库 从库id不能一样######
expire_logs_days = 10
slave_skip_errors = 1062
log_slave_updates = 1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid 

 

从库:

[mysqld]
join_buffer_size = 128M 
sort_buffer_size = 2M 
read_rnd_buffer_size = 2M 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
lower_case_table_names = 1
innodb_strict_mode = 1  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
symbolic-links=0 
character_set_server = utf8 
log-bin = mysql-bin
binlog_format=row
#####主库 从库id不能一样######
server-id = 3
#####主库 从库id不能一样######
#######从库必须######
read-only = 1


expire_logs_days = 10
slave_skip_errors = 1062
log_slave_updates = 1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid 




 

 

 通过以下命令新建库:
create database demo;


   分别输入以下两条命令创建数据表:
use demo;

CREATE TABLE IF NOT EXISTS `users` (`id` INT UNSIGNED AUTO_INCREMENT, `firstname` VARCHAR(100) NOT NULL, `lastname` VARCHAR(100) NOT NULL, PRIMARY KEY(`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;

   输入以下整段命令在数据表中插入数据:
insert into users values (1, 'Kelton', 'Hoover'); 
insert into users values (2, 'Siena', 'Alvarado'); 
insert into users values (3, 'Zayden', 'Bennett'); 
insert into users values (4, 'Kinley', 'Carter');
insert into users values (5, 'Athena', 'Wallace');
insert into users values (6, 'Gavin', 'Duran'); 
insert into users values (7, 'Alyssa', 'Oneill'); 
insert into users values (8, 'Kylan', 'Branch');
insert into users values (9, 'Nigel', 'Rodriguez');
insert into users values (10, 'Mackenzie', 'Watson');
insert into users values (11, 'Cara', 'Richard');
insert into users values (12, 'Madyson', 'Macdonald'); 
insert into users values (13, 'Jordin', 'Stanton');
insert into users values (14, 'Sidney', 'Dean');
insert into users values (15, 'Lainey', 'Skinner'); 
insert into users values (16, 'Adalyn', 'Sellers');
insert into users values (17, 'Alisson', 'Blake'); 
insert into users values (18, 'Jamarcus', 'Clarke');
insert into users values (19, 'Yoselin', 'Yoder'); 
insert into users values (20, 'Holden', 'Bridges');


   然后通过命令 show databases;查看公告新建的库是否已经正常同步:
show databases;

   使用demo 库:
use demo

查看表数据:
select * from users;