mysql 常用

修改数据库密码

set password for root@localhost = password('root');

数据库调优：

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=2G

与 MyISAM 不同，InnoDB 使用缓冲池来缓存索引和行数据。你将这个（缓冲池）设置得越大，访问表中数据所需的磁盘 I/O 就越少。在专用数据库服务器上，你可以将此参数设置为机器物理内存大小的 80%。不过，不要将其设置得过大，因为对物理内存的竞争可能会导致操作系统进行分页操作。请注意，在 32 位系统中，每个进程可能会被限制在 2 至 3.5G 的用户级内存，所以不要将其设置得过高。

使用yum命令安装mysql8

sudo wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm


sudo yum install --nogpgcheck mysql-community-server

sudo systemctl start mysqld
sudo grep 'temporary password' /var/log/mysqld.log

sudo mysql_secure_installation


 

mysql -uroot -p
use mysql;

#配置远程登录
CREATE USER 'root'@'%' IDENTIFIED BY '123456'; 
GRANT ALL ON *.* TO 'root'@'%'; 
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

FLUSH PRIVILEGES;



 
停止所有docker
docker stop $(docker ps -a -q)


删除所有docker
docker rm $(docker ps -a -q)

 
scp db-backup-file.tar.gz root@192.168.1.2:/home
tar -xzvf db-backup-file.tar.gz
mysql -u root -p db-name < db-backup-file.sql