时间:2021-07-01 10:21:17 帮助过:33人阅读
- <code>#rpm -qa | grep mysql
- #rpm -qa | grep mariadb
- #mariadb-libs-5.5.65-1.el7.x86_64
- #yum -y remove mariadb-libs-5.5.65-1.el7.x86_64</code>
创建mysql用户组和mysql用户
- <code># groupadd mysql
- #useradd -g mysql -s /sbin/nologin mysql</code>
解压安装包
- <code>#tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
- # cd /usr/local/
- # mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql</code>
设置mysql环境变量
- <code># echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
- # source /etc/profile</code>
配置mysql
- <code>#mkdir -pv /data/mysql
- #chown mysql.mysql /data/mysql
- # chmod go-rwx /data/mysql</code>
初始化数据库并注意日志里面输出的默认密码#mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
配置启动脚本
- <code># cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
- # chkconfig --add mysqld
- # chkconfig mysqld on
- # chkconfig --list |grep mysqld</code>
编辑mysql配置文件/etc/my.cnf
- <code>[client]
- port = 3306
- socket = /tmp/mysql.sock
- [mysqld]
- port = 3306
- socket = /tmp/mysql.sock
- pid_file = /data/mysql/mysql.pid
- datadir = /data/mysql
- default_storage_engine = InnoDB
- max_allowed_packet = 512M
- max_connections = 2048
- open_files_limit = 65535
- skip-name-resolve
- lower_case_table_names=1
- character-set-server = utf8mb4
- collation-server = utf8mb4_unicode_ci
- init_connect=‘SET NAMES utf8mb4‘
- innodb_buffer_pool_size = 512M
- innodb_log_file_size = 1024M
- innodb_file_per_table = 1
- innodb_flush_log_at_trx_commit = 0
- key_buffer_size = 64M
- log-error = /data/mysql/mysql_error.log
- log-bin = /data/mysql/mysql-bin
- binlog_format = mixed
- expire_logs_days = 10
- slow_query_log = 1
- slow_query_log_file = /data/mysql/slow_query.log
- long_query_time = 1
- server-id=1</code>
启动mysql
- <code>vi /etc/init.d/mysqld
- basedir=/usr/local/mysql //在第46行
- datadir=/data/mysql
- # /etc/init.d/mysqld start
- Starting MySQL.Logging to ‘/data/mysql/localhost.localdomain.err‘.
- SUCCESS! </code>
常见错误排除:
若出现无法登陆数据库可以在/etc/my.cnf最后一行加入skip-grant-tables,重启mysql后直接mysql登陆数据库,
- <code>mysql> use mysql;
- mysql> update?MySQL.user set authentication_string=password(‘root‘) where user=‘root‘ ;
- mysql> flush privileges;</code>
更新数据库密码后删除掉my.cnf最后一行的skip-grant-tables,重启数据库验证新密码是否有效
两台数据库同样方式进行安装,安装完成开始进行主从同步的配置
master:192.168.100.10
slave:192.168.100.20
注意:从数据库服务器上一定要有主数据库服务器上的库、表且表结构要一致。
配置master数据库服务器
- <code>#ls -lrt /data/mysql
- total 2121876
- -rw-r----- 1 mysql mysql 56 May 23 16:06 auto.cnf
- drwxr-x--- 2 mysql mysql 8192 May 23 16:06 performance_schema
- drwxr-x--- 2 mysql mysql 4096 May 23 16:06 mysql
- drwxr-x--- 2 mysql mysql 8192 May 23 16:06 sys
- -rw-r----- 1 mysql mysql 29924 May 23 16:40 localhost.localdomain.err
- -rw-r----- 1 mysql mysql 1073741824 May 23 16:41 ib_logfile1
- -rw-r----- 1 mysql mysql 329 May 23 16:43 mysql-bin.000001
- -rw-r----- 1 mysql mysql 718 May 23 16:46 mysql-bin.000002
- -rw-r----- 1 mysql mysql 872 May 23 17:25 mysql-bin.000003
- -rw-r----- 1 mysql mysql 177 May 23 17:43 mysql-bin.000004
- drwxr-x--- 2 mysql mysql 52 May 23 17:54 zs
- -rw-r----- 1 mysql mysql 666 May 23 18:00 mysql-bin.000005
- -rw-r----- 1 mysql mysql 300 May 23 18:00 ib_buffer_pool
- -rw-r----- 1 mysql mysql 1104 May 23 18:00 slow_query.log
- -rw-r----- 1 mysql mysql 174 May 23 18:00 mysql-bin.index
- -rw-r----- 1 mysql mysql 5 May 23 18:00 localhost.localdomain.pid
- -rw-r----- 1 mysql mysql 12582912 May 23 18:00 ibtmp1
- -rw-r----- 1 mysql mysql 41881 May 23 18:05 mysql_error.log
- -rw-r----- 1 mysql mysql 726 May 23 19:14 mysql-bin.000006
- -rw-r----- 1 mysql mysql 12582912 May 23 19:15 ibdata1
- -rw-r----- 1 mysql mysql 1073741824 May 23 19:15 ib_logfile0
- #vi /etc/my.cnf
- #log-bin=mysql-bin.000006
- server_id=1 #主库为1 从库为2</code>
修改过my.cnf后重启数据库
登陆数据库执行下面的操作:
mysql> grant all privileges on . to ‘test‘@‘%‘ identified by ‘test‘ with grant option;
配置slave数据库服务器
- <code>#vim /etc/my.cnf
- server_id=2
- log-bin=slavelog #可开可不开</code>
重启数据库
管理员登陆
- <code>mysql> change master to
- -> master_host="192.168.100.10",
- -> master_user="test",
- -> master_password="test",
- -> master_log_file="mysql-bin.000006", 主查询show master start;
- -> master_log_pos=0;
- mysql> show slave status\G;
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes #两个进程状态为yes
- mysql>show slave status;</code>
- <code>
- 工作原理
- slave_io_running:yes
- 连接主数据库服务器,并把主数据库服务器binlog日志里sql语句拷贝本机的relybinlog日志里。
- slave_sql_running:yes
- 执行本机relybinlog日志里的sql语句,把数据写到数据库里
- 四、测试主从同步
- 在主服务器上建库建表删除数据,从服务器会跟着同步;
- /var/lib/msyql/
- master.info 记录连接主数据库服务器的信息
- relay-log.info 记录中继binlog日志信息
- localhost-relay-bin.000001
- localhost-relay-bin.000002 中继binlog日志
- localhost-relay-bin.index 保存已有中继binlog日志文件名
- 常用的其他选项
- 适用于master服务器
- binlog-do-db=name 设置master对哪些库记日志
- binlog-ignore-db=name 设置master对哪些库不记日志
- log-slave-updates 记录从库更新,允许链式复制(A-B-C)
- relay-log=dbsvr2-relay-bin 指定中继日志文件名
- replicate-do-db=mysql 仅复制指定库,其他库将被忽略,此选项可设置多条(省略时复制所有库)
- replicate-ignore-db=test 不复制哪些库,其他库将被忽略,ignore-db与go-db只需选用其中一种
- report-host=dbsvr2 报告给master的主机名或ip地址
- slave-net-timeout=60 出现网络中断时,重试超时(默认60秒)</code>
二进制安装mysql数据库并配置主从同步
标签:efault user with report 服务 常见错误 数据库服务器 engine word