服务器安装
-
1、使用以下命令即可进行mysql安装,注意安装前先更新一下软件源以获得最新版本:
$ sudo apt-get update #更新软件源 $ sudo apt-get install mysql-server #安装mysql5.7
-
2、启动和关闭mysql服务器:
$ service mysql start $ service mysql stop
-
3、确认是否启动成功,mysql节点处于LISTEN状态表示启动成功:
$ sudo netstat -tap | grep mysql
-
4、进入mysql shell界面:
$ mysql -u root -p 没有初始密码的话,cat /etc/mysql/debian.cnf下面区查看,登陆后修改密码 mysql -u debian-sys-maint -p Enter password: 这里是关键点,由于mysql5.7没有password字段,密码存储在authentication_string字段中,password()方法还能用 mysql> show databases; mysql> use mysql; mysql> update user set authentication_string=PASSWORD("xiaomu@2020") where user='root'; mysql> update user set plugin="mysql_native_password"; mysql> flush privileges; mysql> quit;
-
5、配置文件在/etc/mysql/mysql.conf.d/mysqld.cnf,可以根据自身需求进行配置
service mysql restart
-
6、创建只读用户
drop user 'xiaomu'@'%'; create user 'xiaomu'@'%' identified by 'xiaomu@xxx'; flush privileges; grant INSERT,SELECT,UPDATE,CREATE on *.* to 'xiaomu'@'%' identified by 'xiaomu@2020' WITH GRANT OPTION; flush privileges;
-
7、查看用户
use mysql; SELECT user,host FROM `mysql`.user; 如果host不是%说明不能远程连接,需要为该用户设置下 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xiaomu@2020' WITH GRANT OPTION; FLUSH PRIVILEGES;
-
8、mysql 配置文件/etc/mysql/mysql.conf.d/mysqld.cnf;数据存放/var/lib/mysql目录下
-
9、附mysqld.cnf配置
[client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] port = 3306 pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql default-time_zone='+8:00' character_set_server=utf8mb4 collation-server=utf8mb4_unicode_ci character-set-client-handshake=FALSE init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci' skip-host-cache skip-name-resolve max_connections = 500 max_connect_errors = 200 open_files_limit = 8000 max_allowed_packet = 500M max_binlog_size = 100M innodb_buffer_pool_size = 2000M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_file_per_table net_read_timeout = 180 net_write_timeout = 360 wait_timeout = 604800 interactive_timeout = 604800 server-id=1 log-bin=mysql-bin #从库会基于此log-bin来做复制 binlog-format=ROW # 选择row模式 binlog-do-db=label_data #用于读写分离的具体数据库 binlog-do-db=label_db binlog_ignore_db=mysql #不用于读写分离的具体数据库 binlog_ignore_db=information_schema #和binlog-do-db一样,可以设置多个 slow_query_log=1 skip-name-resolve thread_stack = 512k long_query_time=0.4 bind-address = 0.0.0.0 #skip-log-bin # 关闭binlog expire_logs_days=5 # 保留指定天数的binlog !includedir /etc/mysql/conf.d/