Centos安装Mysql数据库和Mysql主从配置 mysql主从

Centos安装mysql

#查看系统中是否已安装mysql软件 yum list installed | grep mysql#删除 yum -y remove mysql-libs.x86_64#下载mysql包 wget http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz#解压 tar -zxvf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz#修改目录名 mv mysql-5.7.16-linux-glibc2.5-x86_64 mysql-5.7.16#创建数据保存目录 mkdir -p /data/datas/mysql/data#查看mysql用户组 cat /etc/group |grep mysql #查看mysql用户 cat /etc/passwd |grep mysql #创建mysql用户组 groupadd mysql#创建mysql并添加到mysql用户组 useradd mysql -g mysql#给mysql用户没有登录权限 usermod -s /sbin/nologin mysql#修改目录的权限为mysql用户 chown -R mysql:mysql /data/apps/mysql-5.7.16 chown -R mysql:mysql /data/datas/mysql#进入目录 cd /data/app/mysql-5.7.16/bin/#初始化安装mysql ./mysqld --user=mysql --basedir=/data/app/mysql-5.7.16/ --datadir=/data/datas/mysql/data --initialize #如果报libaio.so错：yum -y install libaio #初始化mysql 成功之后记住密码root@localhost: LIFt4H-lrZQ+#修改配置文件 cd /data/app/mysql-5.7.16/support-files/ vim mysql.server basedir=/data/app/mysql-5.7.16 datadir=/data/datas/mysql/data#将默认生成的my.cnf备份 mv /etc/my.cnf /etc/my.cnf.bak# 启动mysql成功 ./mysql.server start # 停止mysql ./mysql.server stop

配置mysql

#创建软链接 ln -s /data/app/mysql-5.7.16/bin/mysql /usr/bin/mysql#查看mysql版本 mysql --version#复制配置文件 cp my-default.cnf /data/app/mysql-5.7.16/my.cnfcd /data/app/mysql-5.7.16/ #修改配置文件 vim my.cnf [client] default-character-set = utf8mb4[mysql] default-character-set = utf8mb4[mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SETNAMES utf8mb4'basedir = /data/app/mysql-5.7.16 datadir = /data/datas/mysql/data#设置开机启动 cp /data/app/mysql-5.7.16/support-files/mysql.server /etc/init.d/mysqld#可执行权限 chmod 755 /etc/init.d/mysqld # 确认MySQL自启动 chkconfig --list mysqld#设置MySQL开启自启动 chkconfig mysqld on# 再查看MySQL自启动 chkconfig --list mysqld mysqld0:off 1:off 2:on 3:on 4:on 5:on 6:off　# 如果2--5为on的状态就OK

root密码与远程连接

#启动mysql服务 service mysqld start#初始化mysql用户root的密码 ./bin/mysqladmin -uroot -p'4cSM((-qlNz-' password 'root' #4cSM((-qlNz-为上面初始化mysql生成的随机密码#输入密码进入 mysql -uroot -p#mysql远程授权 #输入密码进入 mysql -uroot -p grant all privileges on *.* to 'root'@'%' identified by 'root'; FLUSH PRIVILEGES; #开放端口 vim /etc/sysconfig/iptables -A INPUT -p tcp -m multiport --dports 3306 -j ACCEPT service iptables restart

主从配置
主配置

cd /data/app/mysql-5.7.16/#修改配置 vim my.cnf port = 3306 server_id = 1 #服务id，一般为ip后三位 binlog-do-db = beyond #要同步的数据库 #binlog-ignore-db = mysql,sys,information_schema,performance_schema #不用同步的数据库,多个以逗号分隔 log-bin = mysql-bin #开启log-bin#其他配置优化 max_binlog_size = 500M binlog_cache_size = 2M max_binlog_cache_size = 4M expire_logs_days = 30 max_connections = 500 max_connect_errors = 10000 table_open_cache = 256 long_query_time = 1 slow-query-log #慢sql打印 slow_query_log_file = /data/datas/mysql/data/slow_query_log_file.log#重启 service mysqld restart #创建一个主从同步的用户 mysql -uroot -p create user 'repl'@'%' identified by '123456'; #授权 grant replication slave on *.* to 'repl'@'%' identified by '123456'; flush privileges; show master status; #查看状态

从配置

cd /data/app/mysql-5.7.16/ #修改配置 vim my.cnf port = 3306 server_id = 2 #服务id，一般为ip后三位 read_only = 1 #只读#其他配置优化 log-bin = mysql-bin max_binlog_size = 500M binlog_cache_size = 2M max_binlog_cache_size = 4M expire_logs_days = 30 max_connections = 500 max_connect_errors = 10000 table_open_cache = 256 long_query_time = 1 slow-query-log #慢sql打印 slow_query_log_file = /data/datas/mysql/data/slow_query_log_file.logrelay_log = /data/datas/mysql/data/mysqld-relay-binrelay_log-index = /data/datas/mysql/data/mysqld-relay-bin.index#重启服务 service mysqld restart mysql -uroot -p #设置同步 change master to master_host='主的ip', master_port=3306, master_user='repl', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=780; #mysql-bin.000002和780是从主里查的，show master status; 命令查看#启动从库复制线程 start slave; #查看状态 show slave status; #主要检查两个参数：Slave_IO_Running和Slave_Sql_Running。这两个值为Yes，OK从库配置好了#接下来在 beyond数据库的操作都会同步到从数据库