1.清理Centos7自带mariadb
rpm -qa |grep -i mysql
rpm -qa |grep -i mariadb
yum remove mariadb-libs-5.5.56-2.el7.x86_64
2.rpm安装MySQL
rpm -ivh MySQL-server-5.5.58-1.el6.x86_64.rpm --force --nodeps
rpm -ivh MySQL-client-5.5.58-1.el6.x86_64.rpm --force --nodeps
验证：#mysqladmin --version
3.配置MySQL
a.启动MySQL服务
service mysql start
b.查看MySQL状态
service mysql status
c.设置密码
/usr/bin/mysqladmin -u root password root
d.登录MySQL
mysql -uroot -p
e.开启MySQL远程访问
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
mysql> flush privileges; //刷新设置立即生效
mysql> exit //退出
f.Windows远程访问
mysql -uroot -proot -h192.168.2.128 -P3306
4.常见命令
a.启动命令
service mysql start
b.关闭命令
service mysql stop
c.重启命令
service mysql restart
d.查看服务状态
service mysql status
e.查看MySQL系统配置
cat /etc/my.cnf
5.MySQL服务：常见报错/var/lib/mysql/mysql.sock不存在
a.手动启动服务
/etc/init.d/mysql start
b.开机自启
chkconfig mysql on
c.关闭开机自启
chkconfig mysql off
d.检查开机自启
ntsysv
1.3.2 配置bigdata01
1.配置master(windows) my.ini
[mysqld]
#master的id
server-id=1
#binary log
log-bin="D:/software/sql/MySQL/data/mysql-bin"
#异常信息的日志
log-error="D:/software/sql/MySQL/data/mysql-error"
#指定需要同步哪些数据库（默认全部数据库）
#binlog-do-bo=mydb01
#指定需要 不同步哪些数据库（排除）
binlog-ignore-db=mysql
# The TCP/IP Port the MySQL Server will listen on
port=3306
#Path to installation directory. All paths are usually resolved relative to this.
basedir="D:/software/sql/MySQL/"
#Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
2.配置slave(bigdata01) /etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db=mydb01
replicate-do-db=mydb02
3.windwos/centos需要在配置完毕后，重启电脑
4.设置主从关系
a.windows中查看master的信息
sql> show master status;
b.bigdata01指定master
sql> change master to
master_host='192.168.2.2',
master_user='root',
master_password='root',
master_port=3306,
master_log_file='mysql-bin.000004',
master_log_pos=829;
5.开启主从同步
a.查看server_id
sql> show variables like 'server_id';
b.永久设置server_id
sql> set global server_id=2;
c.开启slave
sql> start slave;
d.确保Slave_IO_Running / Slave_SQL_Running开启
sql> show slave status \G
1.3.3 测试bigdata01
6.尝试在master中mydb01/mydb02中创建表、插入数据，然后测试slave中是否有相应的表和数据
a.window
sql> create database mydb01;
sql> create database mydb02;
sql> use mydb01;
sql> create table student(id int(4), name varchar(10));
sql> use mydb02;
sql> create table student(id int(4), name varchar(10));
sql> insert into student(id, name) values(1, 'zs'); --验证是否主从同步
b.bigdata01
sql> create database mydb01;
sql> create database mydb02;
sql> use mydb01;
sql> create table student(id int(4), name varchar(10));
sql> use mydb02;
sql> create table student(id int(4), name varchar(10));
sql> select * from student; --验证是否主从同步
7.注意：每次要获取最新的File、Position
a.windows中查看master的信息
sql> show master status;
b.关闭slave
sql> stop slave;
c.重置slave
sql> reset slave;
d.bigdata01指定master
sql> change master to
master_host='192.168.2.2',
master_user='root',
master_password='root',
master_port=3306,
master_log_file='mysql-bin.000004',
master_log_pos=2940;
e.启动slave
sql> start slave;
f.确保Slave_IO_Running / Slave_SQL_Running开启
sql> show slave status \G
8.常见报错：数据库之前已经设置过了主从同步
1.ERROR 1201 :could not initlze master info structure ...
2.解决：关闭并重置slave
sql> slave stop;
sql> reset slave;
sql> change master to （检查最新的File、Position）
9.常见报错：数据库之前已经设置过了主从同步，并且也已经开启了主从同步
1.ERROR 1198 : ..... RUN STOP SLAVE ..
2.解决：关闭并重置slave
sql> slave stop;
sql> reset slave;
sql> change master to （检查最新的File、Position）
