mysql安装集合

warrah

已于 2022-03-24 07:16:08 修改

阅读量2.4k

点赞数

分类专栏：岁月云——Web系统最佳实践文章标签： mysql

于 2016-10-23 14:17:16 首次发布

本文链接：https://blog.csdn.net/warrah/article/details/52894469

版权

岁月云——Web系统最佳实践专栏收录该内容

117 篇文章 0 订阅

订阅专栏

1 阿里云linux环境mysql rpm安装

执行命令rpm -qa | grep -i mysql,检查Linux服务器中Mysql及相关RPM包
通过rpm -e对rpm保进行删除
rpm e
卸载出现下面的问题，可参考Linux安装卸载Mysql数据库

执行rpm -ivh MySQL-shared-compat-advanced-5.6.22-1.el7.x86_64.rpm,提示错误

error: Failed dependencies:
        mariadb-libs is obsoleted by MySQL-shared-compat-advanced-5.6.22-1.el7.x86_64

mariadb
分析原因，是要卸载掉MariaDB,因为CentOS的默认数据库已经不再是MySQL了，而是MariaDB
MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。
执行命令rpm -qa | grep mariadb，检查到mariadb的rpm
mariadb
执行命令rpm -e --nodeps mariadb-libs-5.5.40-1.el7_0.x86_64进行卸载
然后执行命令

 rpm -ivh MySQL-shared-compat-advanced-5.6.22-1.el7.x86_64.rpm
 rpm -ivh MySQL-client-advanced-5.6.22-1.el7.x86_64.rpm
 rpm -ivh MySQL-devel-advanced-5.6.22-1.el7.x86_64.rpm
 rpm -ivh MySQL-embedded-advanced-5.6.22-1.el7.x86_64.rpm
 rpm -ivh MySQL-server-advanced-5.6.22-1.el7.x86_64.rpm
 rpm -ivh MySQL-shared-advanced-5.6.22-1.el7.x86_64.rpm
 rpm -ivh MySQL-test-advanced-5.6.22-1.el7.x86_64.rpm

执行命令，修改mysql的密码

#修改配置文件位置
cp /usr/share/mysql/my-default.cnf /etc/my.cnf
#初始化MySQL及设置密码
/usr/bin/mysql_install_db
service mysql start
#查看root账号密码
cat /root/.mysql_secret 
# The random password set for the root user at Wed Dec 11 23:32:50 2013 (local time): qKTaFZnl

修改mysql的默认密码

/etc/init.d/mysql stop   (service mysqld stop )

/usr/bin/mysqld_safe --skip-grant-tables

另外开个SSH连接

[root@localhost ~]# mysql

mysql>use mysql

mysql>update user set password=password("test1234") where user="root";

mysql>flush privileges;

mysql>exit



#pkill -KILL -t pts/0 可将pts为0的**用户(之前运行mysqld_safe的用户窗口)强制踢出

正常启动 MySQL：/etc/init.d/mysql start   (service mysqld start)

允许远程访问

01
mysql> use mysql;
02
mysql> select host,user,password from user;
03
+-----------------------+------+-------------------------------------------+
04
| host                  | user | password                                  |
05
+-----------------------+------+-------------------------------------------+
06
| localhost             | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
07
| localhost.localdomain | root | *1237E2CE819C427B0D8174456DD83C47480D37E8 |
08
| 127.0.0.1             | root | *1237E2CE819C427B0D8174456DD83C47480D37E8 |
09
| ::1                   | root | *1237E2CE819C427B0D8174456DD83C47480D37E8 |
10
+-----------------------+------+-------------------------------------------+
11
 
12
mysql> update user set password=password('123456') where user='root';
13
mysql> update user set host='%' where user='root' and host='localhost';
14
mysql> flush privileges;
15
mysql> exit

修改mysql默认字符集,将/etc/my.cnf，替换为下面的内容。

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/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.
[client]
port = 3306
default-character-set=utf8
[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port = 3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=1000
[mysql]
default-character-set = utf8

mac电脑mysql密码忘了怎么办

关闭mysql服务器
sudo /usr/local/mysql/support-files/mysql.server stop
也可以在系统偏好里有个MySQL里关闭。
cd /usr/local/mysql/bin    进入目录
sudo su 获取权限
 ./mysqld_safe --skip-grant-tables & 重启服务器
重开个终端，
配置短命令：

alias mysql=/usr/local/mysql/bin/mysql

输入mysql 进入mysql命令模式
use mysql进入mysql数据库
flush privileges;大概就是获取权限，要不他不让你改。
set password for 'root'@'localhost'=password('新密码'); 完成修改

如果想全局更改别名

cd ~
open -e .bash_profile
# 写入下面的内容
alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin
# 执行source命令使配置生效。
source .bash_profile

2 mac电脑中安装mysql
先通过下面的命令，安装brew，我的mysql版本时8.0.12

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

接着通过brew install mysql,my.cnf配置参考MAC 安装MySQL my.cnf配置文件，但如果你想理解里面配置的含义，可以参见mysql my.cnf的相关配置，这里面写的相当详细。
按照上面的配置，确实在localhost里面可以将mysql运行起来，但是问题是远程访问存在问题了。
解决mac上mysql不能远程访问的问题，在这篇文章找到了遇到同样问题的人，可喜可乐。实验一下，看看是否有成果。我的下面没有找到
bind-address,防火墙也是关闭的，这又一个诧异的地方。

查看他们说的homebrew.mxcl.mysql.plist,也没有看到bind-address，难道是mysql版本的问题，我现在的问题时8.0.12，我找到的资料版本都是5.x版本。还需继续寻找答案。如果想关闭mysql的开机启动，则需要执行下面的脚本

cd /usr/local/Cellar/mysql/8.0.12
launchctl unload -w ./homebrew.mxcl.mysql.plist

在上图中即使加上bind-address也无济于事。从进程分析来看/etc/my.cnf么有作用，于是将其删掉，mysql照样启动.执行brew list mysql，发现原来brew安装的mysql，配置文件时从/usr/local/Cellar/mysql/8.0.12/.bottle/etc/my.cnf中设置的,更改后重启mysql即可。

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
#bind-address = 127.0.0.1
bind-address=0.0.0.0

java链接mysql8的时候发生错误

caching_sha2_password

故创建用户需要按照下面的方式创建

create user 'your username'@'%' identified with mysql_native_password by 'your password’
flush privileges

接着又出现另外一个问题，这个是因为程序中mysql的驱动版本太低。需要将mysql-connector-java驱动版本升级

Caused by: java.sql.SQLException: Unknown system variable 'tx_read_only'
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996) ~[mysql-connector-java-5.1.34.jar:5.1.34]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) ~[mysql-connector-java-5.1.34.jar:5.1.34]```
如果在mac电脑下安装navicat，可以参考[navicat下载](http://www.pc6.com/mac/111878.html)，
mac允许其他来源软件，参考[Mac打开应用提示已损坏怎么办 Mac安装软件时提示已损坏怎么办](http://www.pc6.com/edu/168719.html)
**3 ubuntu安装mysql**
参考[ubuntu 18.04用apt安装mysql-server](https://www.cnblogs.com/ddif/p/9876502.html)
```sh
# 卸载mysql
sudo apt-get --purge remove mysql-server mysql-common mysql-client
# 安装mysql
sudo apt-get install mysql-server mysql-common mysql-client
# 修改mysql配置
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# 设置监听地址为
bind-address = 0.0.0.0
# 修改root用户的密码
mysql
select user, plugin from mysql.user;
update mysql.user set authentication_string=PASSWORD('000000'),plugin='mysql_native_password' where user='root';
flush privileges;
grant all on *.* to root@'%' identified by '000000' with grant option;
flush privileges;
exit
# 重启mysql
service mysql restart
# 查看mysql是否安装成功
sudo apt-get install net-tools
sudo netstat -tap | grep mysql

mysql密码忘了，还可以通过

修改xampp中的mysql的密码报错，ERROR 1348 (HY000): Column ‘Password’ is not updatable

set password for 'root'@'localhost'=password('123456');
flush privileges;

3 glibc版本
参考mysql安装glibc版本
这个版本不会默认my.cnf，需要自己创建，但是自己创建带来的麻烦是pid文件找不到

The server quit without updating PID file (/appdata/mysql/data/bg1.dzmsoft.com.cn.pid).

按照下面的方式编辑/etc/my.cnf，mysql正常启动了

[mysql] 
# 设置mysql客户端默认字符集 
default-character-set=utf8  
socket=/var/lib/mysql/mysql.sock 
[mysqld] 
#skip-name-resolve 
#设置3306端口 
port = 3306  
socket=/var/lib/mysql/mysql.sock 
# 设置mysql的安装目录 
basedir=/usr/local/mysql 
# 设置mysql数据库的数据的存放目录 
datadir=/appdata/mysql/data 
# 允许最大连接数 
max_connections=200 
# 服务端使用的字符集默认为8比特编码的latin1字符集 
character-set-server=utf8 
# 创建新表时将使用的默认存储引擎 
default-storage-engine=INNODB 
#lower_case_table_name=1 
max_allowed_packet=16M

如果开启了binlog,需要指定server_id否则一直报pid文件找不到,这里参考了mysql 开启binlog,因为涉及到同步，开启之后必须指定server_id,参考mysql复制过程中的server-id的理解

server_id = 1918
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 15

执行show variables like '%log_bin%';可以看到log_bin开启了
在这里插入图片描述
3 mac 安装mariadb
先彻底删除mysql

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*

出现该问题，执行brew update-reset

192:~ dzm$ brew doctor
Traceback (most recent call last):
	11: from /usr/local/Homebrew/Library/Homebrew/brew.rb:31:in `<main>'
	10: from /usr/local/Homebrew/Library/Homebrew/brew.rb:31:in `require_relative'
	 9: from /usr/local/Homebrew/Library/Homebrew/global.rb:80:in `<top (required)>'
	 8: from /usr/local/Homebrew/Library/Homebrew/vendor/portable-ruby/2.6.3_2/lib/ruby/2.6.0/rubygems/core_ext/kernel_require.rb:54:in `require'
	 7: from /usr/local/Homebrew/Library/Homebrew/vendor/portable-ruby/2.6.3_2/lib/ruby/2.6.0/rubygems/core_ext/kernel_require.rb:54:in `require'
	 6: from /usr/local/Homebrew/Library/Homebrew/os.rb:7:in `<top (required)>'
	 5: from /usr/local/Homebrew/Library/Homebrew/os.rb:43:in `<module:OS>'
	 4: from /usr/local/Homebrew/Library/Homebrew/os/mac.rb:65:in `prerelease?'
	 3: from /usr/local/Homebrew/Library/Homebrew/os/mac.rb:28:in `version'
	 2: from /usr/local/Homebrew/Library/Homebrew/os/mac/version.rb:32:in `from_symbol'
	 1: from /usr/local/Homebrew/Library/Homebrew/os/mac/version.rb:32:in `fetch'
/usr/local/Homebrew/Library/Homebrew/os/mac/version.rb:32:in `block in from_symbol': unknown or unsupported macOS version: :dunno (MacOSVersionError)

brew install mariadb
cd /usr/local/Cellar/mariadb/10.7.3/bin



==> Running `brew cleanup cairo`...
Removing: /usr/local/Cellar/cairo/1.16.0... (117 files, 5.5MB)
==> Checking for dependents of upgraded formulae...
==> No broken dependents found!
==> Caveats
==> mariadb
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To restart mariadb after an upgrade:
  brew services restart mariadb
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mariadb/bin/mysqld_safe --datadir=/usr/local/var/mysql

==> Downloading https://mirrors.aliyun.com/homebrew/homebrew-bottles/mariadb-10.
######################################################################## 100.0%
Error: Cannot install mariadb because conflicting formulae are installed.
  mysql: because mariadb, mysql, and percona install the same binaries

192:local dzm$ brew unlink mysql
Unlinking /usr/local/Cellar/mysql/8.0.22_1... 92 symlinks removed.

192:bin dzm$  brew services restart mariadb
==> Successfully started `mariadb` (label: homebrew.mxcl.mariadb

192:bin dzm$ brew reinstall mariadb
==> Downloading https://mirrors.aliyun.com/homebrew/homebrew-bottles/mariadb-10.
Already downloaded: /Users/dzm/Library/Caches/Homebrew/downloads/56154d29324da4960804a63b9ea1eb8332739cd76d773bf54bab8a2e2532bfed--mariadb-10.7.3.monterey.bottle.tar.gz
==> Reinstalling mariadb 
==> Pouring mariadb-10.7.3.monterey.bottle.tar.gz
==> /usr/local/Cellar/mariadb/10.7.3/bin/mysql_install_db --verbose --user=dzm -
Last 15 lines from /Users/dzm/Library/Logs/Homebrew/mariadb/post_install.01.mysql_install_db:

    shell> /usr/local/Cellar/mariadb/10.7.3/bin/mysql -u root mysql
    mysql> show tables;

Try 'mysqld --help' if you have problems with paths.  Using
--general-log gives you a log in /usr/local/var/mysql that may be helpful.

The latest information about mysql_install_db is available at
https://mariadb.com/kb/en/installing-system-tables-mysql_install_db
You can find the latest source at https://downloads.mariadb.org and
the maria-discuss email list at https://launchpad.net/~maria-discuss

Please check all of the above before submitting a bug report
at https://mariadb.org/jira

Warning: The post-install step did not complete successfully
You can try again using:
  brew postinstall mariadb
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To restart mariadb after an upgrade:
  brew services restart mariadb
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mariadb/bin/mysqld_safe --datadir=/usr/local/var/mysql
==> Summary
🍺  /usr/local/Cellar/mariadb/10.7.3: 914 files, 186.8MB
==> Running `brew cleanup mariadb`...
Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP.
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`).

brew services start mariadb
brew services stop mariadb
brew services restart mariadb

我参考MAC安装、启动、初始化、完整卸载Mariadb(MySQL),在

192:bin dzm$ sudo mysql_install_db
Password:

FATAL ERROR: Could not find resolveip

The following directories were searched:

    /usr/local/mysql/bin
    /usr/local/mysql/bin

If you compiled from source, you need to either run 'make install' to
copy the software into the correct location ready for operation.
If you don't want to do a full install, you can use the --srcdir
option to only install the mysql database and privilege tables.

If you are using a binary release, you must either be at the top
level of the extracted archive, or pass the --basedir option
pointing to that location.

The latest information about mysql_install_db is available at
https://mariadb.com/kb/en/installing-system-tables-mysql_install_db