brew install mysql
bash mysql.server start
export PATH=${PATH}:/usr/local/mysql/bin
修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
配置目录
/usr/local/mysql/support-files
完全卸载More
1. brew remove mysql
2. brew cleanup
3. brew install mysql
4. mysql.server start
5. mysql_secure_installation
/usr/local/var/mysql
/opt/homebrew/var/mysql
或者通过brew info mysql查看
curl -L -o mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
yum install -y libaio
# 安装完成后我们还需要为 MySQL 提供用户和用户组来运行(我这里设置为 mysql:mysql):
groupadd mysql && useradd -r -g mysql -s /bin/false mysql
mv -f ~/mysql-5.7.23-linux-glibc2.12-x86_64 /usr/local/mysql
# 然后创建 MySQL 需要的文件目录并设置权限和用户:
mkdir /usr/local/mysql/mysql-files && \
chown mysql:mysql /usr/local/mysql/mysql-files && \
chmod 750 /usr/local/mysql/mysql-files
# 初始化数据库
/usr/local/mysql/bin/mysqld --initialize --user=mysql && \
/usr/local/mysql/bin/mysql_ssl_rsa_setup
2022-06-05T05:19:09.676700Z 1 [Note] A temporary password is generated for root@localhost: EBwlVuaE=7)_
vim /etc/profile
# add following:
export PATH=$PATH:/usr/local/mysql/bin/
source /etc/profile
mkdir /var/log/mariadb
touch /var/log/mariadb/mariadb.log
chown -R mysql:mysql /var/log/mariadb/
# 启动 mysql
mv -f /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql.server
/etc/init.d/mysql.server start
# login
mysql -u root -h 127.0.0.1 -P 3306 -p
set password=password("yourpassword");
flush privileges;
systemctl enable mysqld
systemctl daemon-reload
# 查看是否有存在的MySQL运行进程
[root@localhost src]# netstat -tunpl|grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3095/mysqld
# 关闭MySQL服务
[root@localhost src]# service mysqld stop
# 查看命令所在路径
[root@localhost src]# which mysql
/usr/bin/mysql
# 查看rpm安装目录
[root@localhost src]# rpm -qf /usr/bin/mysql
mysql-5.0.95-5.el5_9
# 无依赖删除软件包
[root@localhost src]# rpm -e mysql-5.0.95-5.el5_9 --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
yum -y install gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel* make cmake curl freetype libjpeg-turbo libjpeg-turbo-devel openjpeg-libs libpng gd ncurses
useradd mysql -s /sbin/nologin
tar -zxvf mysql-5.5.29.tar.gz && cd mysql-5.5.29 #默认情况下是安装在/usr/local/mysql
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DSYSCONFDIR=/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make && make install
chown -R mysql.mysql /usr/local/mysql
cd /usr/local/src/mysql-5.5.29/support-files
# 注册服务
cp mysql.server /etc/rc.d/init.d/mysqld
# 使用默认配置文件
cp my-small.cnf /etc/my.cnf
# 让chkconfig管理mysql服务
chkconfig --add mysqld
# 给mysqld授权执行
chmod a+x /etc/init.d/mysqld
# 或者使用 setfacl -m u:root:rwx /etc/init.d/mysqld
# 开机启动
chkconfig mysqld on
cd /usr/local/mysql/scripts
./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
service mysqld start
ln -s /usr/local/mysql/bin/mysql /usr/local/sbin/mysql
cd ~
另外
# 把path添加到当前用户目录的bashrc中,如果需要全局设定,请修改`/etc/profile` vi .bashrc #加入以下内容
PATH=/usr/local/mysql/bin:$PATH
source /etc/profile
/usr/local/mysql/bin/mysqladmin -u root password 'aaaaaa'
SHOW VARIABLES LIKE 'character%'
修改mysql的my.cnf文件增加如下内容
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
pid-file=/var/lib/mysql/mysqld
[mysql]
default-character-set=utf8
[mysqld_safe]
log-error=/var/lib/mysql/mysql_error.err
# vi /etc/my.cnf
在[mysqld]的段中加上一句skip-grant-tables,eg:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
保存并且退出vi。重启mysql
# service mysqld restart
登录并修改MySQL的root密码
mysql
use mysql;
UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root';
flush privileges;
将MySQL的登录设置修改回来
vim /etc/my.cfg
service mysqld restart # 然后重启mysqld
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 D:\\Program Files\\MySQL\\MySQL Server 5.0\\bin
mysqld-nt --skip-grant-tables
mysqldump -uroot -pmypassword wack
> wack.sql
root - 用户名
mypassword - 密码
wack -数据库名
wack.sql - 导出的文件名
导出到的wack.sql在当前执行命令的目录下。
命令:mysqldump -u 用户名 -p 数据库名 > 文件名
如果用户名需要密码,则需要在此命令执行后输入一次密码核对;如果数据库用户名不需要密码,则不要加“-p”参数,导入的时候相同。注意输入的用户名需要拥有对应数据库的操作权限,否则无法导出数据。由于是作系统维护和全部数据库的导出,一般我们使用root等超级用户权限。
比如要将abc这个数据库导出为一个文件名为db_abc.sql的数据库文件到当前目录下,则输入下面的命令:
# mysqldump -u root -p abc > db_abc.sql
导出到excel
my.ini设置
secure-file-priv=''
就可以在任意目录下操作了More
select * from user into outfile "C:/ProgramData/MySQL/MySQL Server 5.5/Uploads/2.xls"
接下来输入root用户的密码即可导出数据库。
命令:mysql -u 用户名 -p 数据库名 < 文件名
同mysqldump命令一样的用法,各参数的意义同mysqldump。
比如我们要将/root/backup/db_abc.sql这个文件的数据导入到abc数据库中,则使用下面的命令:
# mysql -u root -p abc < /root/backup/db_abc.sql
之后输入root用户的密码即可导入。值得一提的是,如果这个数据库本身不存在,则无法导入。也就是说要想导入到一个数据库,至少应该先建立一个空的数据库,然后再导入。
# mysql -V //大写
先查询mysql的版本
# rpm -q --queryformat "%{name}.%{arch}\n" mysql
mysql.i386
mysql.x64_64
卸载
rpm -e mysql.i386
rpm -e mysql.x86_64
GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'your paaaword';
for mysql >=8.0, GRANT ALL PRIVILEGES ON *.* TO root@'%';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
某个用户的权限
show grants for 'your-username'@'%';
赋予用户对某个数据库的权限
grant all privileges on `database-name`.* to your-name@'%' identified by password 'your-password';
`your-password`是通过select password('明文密码')得到的41位加密文密码.
或者直接明文密码,自动转换为加密密码
grant all privileges on `database-name`.* to your-name@'%' identified by 'your-clear-text-password';
ALL PRIVILEGES可替换为 create[,update[,select]]等
赋予用户数据库所有权限
grant all on *.* to dba@'localhost'
select cat_type,count(*) as count from cat group by cat_type having count>1;
update a inner join b on b.uid = a.id set a.gv = 1;
update user set password=password("mypassword") where user="root";
# comment
-- comment
/*comment*/
导入
> mysql -uroot -pmypassword
// #root -用户名
// #mypassword -密码
> use wack
// #wack -数据库名
> set names utf8 source c:\wack.sql
>
// source c:\wack.sql末尾不能有分号
修改查询编码格式
show variables like '%char%';
set character_set_results=gbk;
时间转换
select from_unixtime('1462895999');
select UNIX_TIMESTAMP('2006-11-04 12:23:00');
show global variables like ‘wait_timeout’;
set global wait_timeout=288000;
show warnings;
show errors;
show tables;
show profiles; - 显示操作时间
删除表内所有记录:TRUNCATE TABLE w_user;
返回第4行的1条记录:SELECT * FROM w_user LIMIT 3,1;
show variables like 'default_storage_engine';
SHOW variables like "have_%";
重命名列名:alter table w_dish change wcomments_num wsales int unsigned not null default 0;
重命名表名:alter table w_dish rename to category2;
删除unique约束:alter table w_classify drop index wname;
inner join:select * from w_user inner join w_store on(w_store.w_u_wid = w_user.wid) where wusername='jxtgddlt';
在insert中插入select语句:insert into w_dish (w_c_wid, w_s_wid, wname, wprice, wleft_num)values((select wid from w_classify where wname = '新分类元'), (select wid from w_store where wstore_id = 8978971), '插入成功啦竟然', 100, 100);
drop table w_user;
insert into procedure_storage(param1, param2, idDesc) values('100000', '', 'generateID');
-- 需要加分号
有懒加载情况下, HibernateUtil.closeSession();不要滥用。\ 比如一些select语句是不用closesession的,而update和insert等就要用closesession了。
UTF-8字符集编码有可能是2, 3, 4个字节。但MySQL中的utf8字符集,最多只能存储3个字节的字符,除Emoji表情之外,还有一些生僻字是需要4个字节的.
Unknown column 'Password'
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'
Mysql5.7没有字段password,已修改为authentication_string
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql_upgrade -u root -p;
权限不够,请使用root权限的用户登录SQL.
show global variables like 'max_allowed_packet';
set global max_allowed_packet=1024*1024*1024;
可能是端口没开启
/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
ERROR! The server quit without updating PID file
是因为建立的磁盘镜像APFS(区分大小写),改成APFS即可。
'/opt/homebrew/opt/icu4c/lib/libicuuc.72.dylib' (no such file)
Solution:
brew reinstall icu4c