MariaDB [(none)]> create user yuchao@'127.0.0.1' identified by 'redhat123';
MariaDB [(none)]> use mysql;
MariaDB [mysql]> select host,user,password from user where user=‘yuchao’;
切换普通用户yuchao,查看数据库信息,发现无法看到完整的数据库列表
[root@master ~]# mysql -uyuchao -p -h 127.0.0.1
MariaDB [(none)]> show databases;
数据库权限设置
mysql使用grant命令对账户进行授权,grant命令常见格式如下
grant 权限 on 数据库.表名 to 账户@主机名 对特定数据库中的特定表授权
grant 权限 on 数据库.* to 账户@主机名 对特定数据库中的所有表给与授权
grant 权限1,权限2,权限3 on *.* to 账户@主机名 对所有库中的所有表给与多个授权
grant all privileges on *.* to 账户@主机名 对所有库和所有表授权所有权限
退出数据库,使用root登录,开始权限设置
[root@master ~]# mysql -uroot -p
MariaDB [(none)]> use mysql;
MariaDB [(none)]> grant all privileges on . to yuchao@127.0.0.1;
MariaDB [mysql]> show grants for yuchao@127.0.0.1;
移除权限
MariaDB [(none)]> revoke all privileges on *.* from yuchao@127.0.0.1;
远程连接设置哦设置所有库,所有表的所有权限,赋值权限给所有ip地址的root用户 mysql > grant all privileges on *.* to root@'%' identified by 'password'; #创建用户 mysql > create user 'username'@'%' identified by 'password'; #刷新权限 flush privileges;
#查看数据库状态
systemctl status mariadb
#停mariadb
systemctl stop mariadb
#修改配置文件 vim /etc/my.cnf #修改内容 #解释:server-id服务的唯一标识(主从之间都必须不同);log-bin启动二进制日志名称为mysql-bin
[mysqld] server-id=1 log-bin=mysql-bin
#重启mariadb systemctl start mariadb
master主库添加从库账号
1.新建用于主从同步的用户chaoge,允许登录的从库是'192.168.178.130'
create user 'chaoge'@'192.168.178.130' identified by 'redhat';
2.#题外话:如果提示密码太简单不复合策略加在前面加这句 mysql> set global validate_password_policy=0;
3.给从库账号授权,说明给chaoge从库复制的权限,在192.168.178.130机器上复制 grant replication slave on . to ‘chaoge’@‘192.168.178.130’; #检查主库创建的复制账号 select user,host from mysql.user; #检查授权账号的权限 show grants for chaoge@‘192.168.178.130’;
实现对主数据库锁表只读,防止数据写入,数据复制失败 flush table with read lock;
4.检查主库的状态
MariaDB [(none)]> show master status -> ; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 575 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: No
有问题了,Slave_SQL_Running应该是Yes才对。
再往下看,有错误的提示:
Last_Errno: 1053 Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: ‘INSERT INTO hx_stat_record …(一句SQL语句)’
这里有说明要怎么操作了:)
先stop slave,然后执行了一下提示的语句,再SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
mysql错误日志: 100512 9:13:17 [Note] Slave SQL thread initialized, starting replication in log ‘mysqlmaster.000079’ at position 183913228, relay log ‘./hx-relay-bin.002934’ position: 183913371 100512 9:13:17 [Note] Slave I/O thread: connected to master ‘replicuser@192.168.1.21:3306’, replication started in log ‘mysqlmaster.000079’ at position 183913228 100512 9:13:17 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236) 100512 9:13:17 [ERROR] Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log 100512 9:13:17 [Note] Slave I/O thread exiting, read up to log ‘mysqlmaster.000079’, position 183913228