mysql 安装
Linux下安装mysql数据库
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
然后使用tar命令将服务器下载好的安装包解压在当前目录
ln -s mysql-5.7.26-linux-glibc2.12-x86_64 /var/lib/mysql
mv mysql-5.7.26-linux-glibc2.12-x86_64 /var/lib/mysql
groupadd mysql
useradd -r -g mysql mysql
初始化数据库
5.7.6之后的版本初始化数据库不再使用mysql_install_db，而是使用： bin/mysqld --initialize
bin/mysqld --initialize --user=mysql --basedir=/var/lib/mysql --datadir=/data/mysql_db
mysql 5.7版本初始化数据库后会默认生成一个初始的登录密码，第一次登录要使用初始密码
信息如下:
2018-10-24T05:46:33.446315Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-10-24T05:46:33.448636Z 0 [Warning] You need to use --log-bin to make --binlog-format work.
2018-10-24T05:46:34.733390Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-10-24T05:46:34.912448Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-10-24T05:46:34.979791Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2ac659b8-d750-11e8-8fa1-fa163efff95f.
2018-10-24T05:46:34.982104Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-10-24T05:46:34.982589Z 1 [Note] A temporary password is generated for root@localhost: !+auH/*Ow7_j
root@localhost: 后面的一字符串 !+auH/*Ow7_j 就是后面登录密码
配置my.cnf
mysql 5.7.6版本 mysql-5.7.26/support-files/下不存在my-default.cnf文件,需要在/etc/目录下创建my.cnf,并写入以下内容
basedir = /web/mysql-5.7.26/
datadir = /web/mysql-5.7.26//data
port = 3306
socket = /web/mysql-5.7.26/data/mysql.sock
log-error=/web/mysql-5.7.26/logs/mysql.log
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
将mysqld服务加入开机自启动项
开机自启动
cp {mysql}/support-files/mysql.server /etc/init.d/mysqld
如果不按照上述操作,就只能使用{mysql}/bin/mysqld_safe & 命令来启动服务
chkconfig --add mysql
serivce mysql start
service mysql stop
-----centos 7 ------
vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Community Server
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
Alias=mysql.service
[Service]
User=mysql
Group=mysql
#systemctl status就是根据pid来判断服务的运行状态的
PIDFile=/data/mysql/mysql.pid
# 以root权限来启动程序
PermissionsStartOnly=true
# 设置程序启动前的必要操作。例如初始化相关目录等等
#ExecStartPre=/data/mysql/bin/mysql-systemd-start pre
# 启动服务
ExecStart=/data/mysql/bin/mysqld_safe
# 停止服务
ExecStop=/data/mysql/bin/mysqladmin --login-path=local shutdown
# Don't signal startup success before a ping works
#ExecStartPost=/data/mysql/bin/mysql-systemd-start post
# Give up if ping don't get an answer
TimeoutSec=600
#Restart配置可以在进程被kill掉之后，让systemctl产生新的进程，避免服务挂掉
Restart=always
PrivateTmp=false
LimitNOFILE=65535
LimitNPROC=65535
------------------------
systemctl start mysqld
执行 ./mysql -u root -p 会要求输入密码
密码就是在初始化数据库时生成的密码
修改密码
mysql> set password=password("123456");
Query OK, 0 rows affected, 1 warning (0.27 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.26 sec)
退出mysql并重启, 就可以用新密码登录mysql了
如果想要用navicat等连接工具需要授权才能连接
mysql>grant all on *.* to 'remote'@'%' identified by '123456';
主从复制
主库执行:
mysql> grant replication slave on *.* to 'repl0'@'192.168.0.%' identified by '123456';
从库执行：
mysql> CHANGE MASTER TO master_host='192.168.0.121',master_user='repl0',master_password='123456', master_log_file='mysql-bin.000004',master_log_pos=844;
mysql>start slave;
mysql > SHOW SLAVE STATUS G
＝＝＝＝＝＝＝＝＝＝＝问题集锦＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
如果忘记数据库密码的解决方法：
1、初始化空密码： mysqld --initialize-insecure
2、跳过授权列表 （skip-grant-tables） 修改完以后再注释掉。
[mysqld]
skip-grant-tables=1
mysql> set password = PASSWORD('123456');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
解决方法：
先刷新一下权限表。
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> set password = PASSWORD('123456');
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
主从报错
Last_Errno: 1782
Last_Error: Error executing row event: '@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.'
>stop slave sql_thread;
>set GLOBAL GTID_MODE = ON_PERMISSIVE;
]>set GLOBAL GTID_MODE = OFF_PERMISSIVE;
>set GLOBAL GTID_MODE = OFF;
>start slave sql_thread;
>start slave;
Last_Error: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.
Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF.
Last_SQL_Errno: 1781
Last_SQL_Error: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.
>set GLOBAL GTID_MODE = OFF_PERMISSIVE;
>set GLOBAL GTID_MODE = ON_PERMISSIVE;
>set GLOBAL GTID_MODE = ON;
>start slave;
－－－－－－－－－－－
最近在部署MySQL主从复制架构的时候，碰到了"Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work." 这个错误提示。即主从架构中使用了相同的UUID。检查server_id系统变量，已经是不同的设置，那原因是？接下来为具体描述。
master_mysql> show variables like 'server_id';
slave_mysql> show variables like 'server_id';
查看是不同的。
但是查看/mysql/data/auto.cnf发现里面的UUID是哦相同的。原因是mysql是直接从节点1上拷贝过来而导致。
解决：mv /mysql/data/auto.cnf /mysql/data/auto.cnf.bak 重启mysql解决
set global server_id=2;
一、忽略错误后，继续同步。（该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况。）
1)、停止同步
mysql>stop slave;
2)、设置参数
mysql>set global sql_slave_skip_counter =1;
3)、启动同步并查看
start slave;
mysql> show slave statusG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
二、重新做主从，完全同步。（该方法适用于主从库数据相差较大，或者要求数据完全统一的情况。）
1)、进入主库，进行锁表，防止数据写入。注意：该处是锁定为只读状态，语句不区分大小写
mysql> flush tables with read lock;
2)、进行数据备份并解锁
mysqldump -uroot -pYouPwd YourDB > bak.sql；
mysql> UNLOCK TABLES;
3)、查看master 状态并记录
mysql> show master status;
4)、把mysql备份文件传到从库机器，进行数据恢复
5)、停止从库的状态
mysql> stop slave;
6)、从库执行mysql命令，导入数据备份
7)、设置从库同步，具体参数请根据master status填写
change master to master_host = '192.168.1.246', master_user = 'slave', master_port=3306, master_password='YourSalvePwd', master_log_file = 'mysqld-bin.000035', master_log_pos=120;
8)、重新开启从同步
mysql> start slave;
9)、查看同步状态
mysql> show slave statusG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes