一、Mysql安装
MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言–结构化查询语言（SQL）进行数据库管理
1、下载
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
2、解压
tar zxvf mysql-5.7.19.tar.gz #解压
cp -r mysql-5.7.19 /usr/local/mysql #复制
3、添加用户组和用户
groupadd mysql
useradd -g mysql mysql #添加用户mysql 到用户组mysql
4、安装
cd /usr/local/mysql/bin
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
参数详解：
user:用户
basedir:安装根目录
datadir:存放数据目录
二、配置
查看support-files文件夹的内容，发现并没有my_default.cnf默认的配置文件，如果没有默认的配置文件，需要手动创建一个
my_default.cnf配置文件网上找了一个配置文件，如下，上传到support-files文件夹中，当然这个配置文件可以根据需要自行修改
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 一般配置选项
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/mysql.pid
log-error = /usr/local/mysql/data/error.log
port = 3306
socket = /var/run/mysqld/mysqld.sock
# 服务器字符集
character-set-server=utf8
#下面是可选项，要不要都行，如果出现启动错误，则全部注释掉，保留最基本的配置选项，然后尝试添加某些配置项后启动，检测配置项是否有误
back_log = 300
max_connections = 3000
max_connect_errors = 50
table_open_cache = 4096
max_allowed_packet = 32M
#binlog_cache_size = 4M
max_heap_table_size = 128M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
#log-bin=mysql-bin
long_query_time = 6
server_id=1
innodb_buffer_pool_size = 1G
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = on
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
default-character-set=utf8
safe-updates
[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
[client]
#客户端字符集
default-character-set=utf8
2、拷贝配置和设置服务
cp /home/mysql5.7.19/support-files/my_default.cnf /etc/my.cnf
cp /home/mysql5.7.19/support-files/mysql.server /etc/init.d/mysqld
这一步的作用，可以使用
service mysqld start(stop,restart)
开机自启
chkconfig --add mysqld
3、设置权限
chown -R mysql:mysql /usr/local/mysql/data
chown -R mysql:mysql /var/run/mysqld
三、启动服务端
/etc/init.d/mysqld start
报错：
mysqld.serviceFailed to stop mysqld.service: Unit mysqld.service
解决：
systemctl enable mysqld.service
测试：
/etc/init.d/mysqld start(stop,restart)
或者
service mysqld start
四、启动客户端
1、设置全局变量
vi /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
注意：/usr/local/mysql/support-files/mysql.server这里是mysql服务端,/usr/local/mysql/bin/mysql这里是mysql客户端
2、启动mysql -uroot -p
报错：
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
解决
ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock
或者 /etc/my.cnf
socket = /tmp/mysqld.sock
3、忘记密码
/etc/init.d/mysqld stop //停止数据库服务端
mysql>SET SQL_SAFE_UPDATES = 0; //修改数据库模式
mysql>update mysql.user set authentication_string=password('') where user='root';
修改成功
4、退出mysql>\q
五、字符集
1、查看字符编码
show variables like 'character_set%%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
2、修改字符集
character_set_server: 服务器安装时指定的默认字符集设定。
character_set_database: 数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。
建表时候，字段字符集的选取方式如下：
1. * if 字段指定的字符集
2. * else if 表指定的字符集
3. * else if @@character_set_database
4. * else @@character_set_server (如果没有设定，这个值为latin1)
按照网上上面的说法，如果character_set_server和character_set_database变量的值不同，则新建数据库的字符集以character_set_server为准
[mysqld]
character-set-server=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
即使在配置文件里根本就没有配置character_set_database，重启mysql后character_set_database变量值也会和character_set_server保持一致，故只需要配置character_set_server就行了
3、重启
mysql> show variables like 'character_set%%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
修改成功
六、远程连接
1、阿里云打开3306端口安全组
2、navicat连接
报错
ERROR 1130: Host 'xx.xx.xx.xx' is not allowed to connect to this MySQL server
解决
mysql -uroot -p;
1. 改表法。可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从"localhost"改称"%"
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;
2. 授权法。例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。
mysql>GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
如果你想允许用户myuser从ip为192.168.1.3的主机连接到mysql服务器，并使用mypassword作为密码
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'58.213.198.59' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
mysql>flush privileges; 这句一定要加上！！！
navicat连接成功
七、友情链接
mysql下载: https://dev.mysql.com/downloads/mysql/
本文作者: William
本文链接: http://www.williamwqq.site/2017/10/10/mysql-install/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 3.0 许可协议。转载请注明出处！