标签归档:mysql

MySQL 读写分离的主从数据库配置

准备两台服务器,都安装 mysql 服务器。主服务器称为 master, 从服务器称为 slave 。前提要设置好防火墙,保证网络通畅。

master 配置

配置 my.cnf

[mysqld]
#bind-address = 127.0.0.1    注释掉

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log

继续阅读

centos7 安装 mysql

wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
yum localinstall mysql57-community-release-el7-8.noarch.rpm
mysql-community-server

可以修改 /etc/yum.repos.d/mysql-community.repo 源,改变默认安装的mysql版本。比如要安装5.6版本,将5.7源的 enabled=1 改成 enabled=0 。然后再将5.6源的 enabled=0 改成 enabled=1 即可。

phpmyadmin.pma_table_uiprefs doesn’t exist

在使用 phpmyadmin 管理 mysql 时,出现 phpmyadmin.pma_table_uiprefs doesn't exist ,快速解决办法如下:

  1. On the shell: locate create_tables.sql.
  2. import /usr/share/doc/phpmyadmin/examples/create_tables.sql.gz using phpMyAdmin.
  3. open /etc/phpmyadmin/config.inc.php and edit lines 81-92: change pma_bookmark to pma__bookmark and so on.

Mysql彻底卸载重装

sudo apt-get purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean
sudo rm -rf /var/log/mysql
sudo rm -rf /var/log/mysql.*
sudo rm -rf /var/lib/mysql
sudo apt-get install mysql-server --fix-missing --fix-broken

mysql 创建新数据库并添加用户授权

mysql数据库创建用户,并:
– 创建与用户同名的数据库并授予所有权限。
– 给以 用户名_ 开头的数据库 (username_%) 授予所有权限。

# 把 itop 改为对应的用户名

CREATE USER 'itop'@'%' IDENTIFIED WITH mysql_native_password;
GRANT USAGE ON *.* TO 'itop'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
SET PASSWORD FOR 'itop'@'%' = '***';
CREATE DATABASE IF NOT EXISTS `itop` DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
GRANT ALL PRIVILEGES ON `itop`.* TO 'itop'@'%';
GRANT ALL PRIVILEGES ON `itop\_%`.* TO 'itop'@'%';

mysql全局修改sql_mode

  1. mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法,数据校验等。可以通过以下方式查看当前数据库使用的 sql_mode:
show variables like 'sql_mode';

5.7.22 版本中,结果是 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  1. session 修改
set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
  1. 全局修改。打开 mysql.cnf ,在 [mysqld] 下面添加:
sql_mode=

在Ubuntu 18.04上安装MySQL

使用 apt 或 tasksel 安装完 mysql-server 之后,默认无法登录,需要进行配置。

  1. 使用命令 mysql_secure_installation 并按提示操作,之后重启服务。
  2. 使用 sudo mysql -u root -p 登录,密码为空,或上一步设置的密码。
  3. 运行以下SQL: update mysql.user set plugin='mysql_native_password', authentication_string=password('root') where User='root'; ,退出,重启服务。

新密码是 root

mysql 8.0 无法登录的解决办法:

  1. 更改加密方式:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
  1. 更改密码:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
  1. 刷新:
mysql> FLUSH PRIVILEGES;

linux中mariadb安装后phpmyadmin 无法连接的问题怎么解决

There are 2 ways to solve this:

  1. You can set the root user to use the mysql_native_password plugin
  2. You can create a new db_user with you system_user (recommended)

Option 1:

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Option 2: (replace YOUR_SYSTEM_USER with the username you have)

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

账号初始化:

$ sudo mysql_secure_installation

默认root密码为空;然后设置root密码和其他选项:

- Set root password? [Y/n] y
- Remove anonymous users? [Y/n] y
- Disallow root login remotely? [Y/n] y
- Remove test database and access to it? [Y/n] y
- Reload privilege tables now? [Y/n] y

登陆MariaDB命令行:

$ sudo mysql -u root -p

java 连接 mysql 并使用 prepareStatement 查询数据库

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;

import java.sql.*;
import java.util.*;

public class Main {

    public static void main(String[] args) {

        Connection conn = null;
        User user = null;
        Gson gson = new Gson();

        try {
            # JDK6 之前的版本需要加载驱动
            # new com.mysql.jdbc.Driver();

            # 连接数据库
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname?" +
                    "useSSL=false&useUnicode=true&characterEncoding=UTF8", "root", "root");

            # 使用 prepareStatement 查询数据库
            PreparedStatement preStatement = conn.prepareStatement("select * from tables");
            ResultSet res = preStatement.executeQuery();

            List<User> users = new ArrayList<User>();
            while (res.next()) {
                user = new User();
                user.setId(Integer.parseInt(res.getString("id")));
                user.setUser(res.getString("user"));
                users.add(user);
            }
            # 导出为 json
            String strJson = gson.toJson(users);
            System.out.println(strJson);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

继续阅读