Skip to content

資料庫維運

User

How to Create MySQL Users Accounts and Grant Privileges

create a user that can connect from any host, use the '%' wildcard

CREATE USER 'newuser'@'%' IDENTIFIED BY 'user_password';

random password ```bash= openssl rand -base64 12 

### Grant Privileges
Grand all privileges to a user account over a specific database

``` sql
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';


SHOW GRANTS

SHOW GRANTS FOR 'database_user'@'localhost';

Create Database

``sql= CREATE SCHEMA IF NOT EXISTSdatabase_name` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 

## 1040 Too many connections
To get total number of concurrent connections allowed
```mysql=
SHOW VARIABLES LIKE "max_connections";

how many concurrent connections are being used currently ```mysql= SHOW STATUS LIKE "max_used_connections"; 

- [Monitoring used connections on mysql to debug 'too many connections'](https://stackoverflow.com/questions/2453308/monitoring-used-connections-on-mysql-to-debug-too-many-connections)
- [Max_used_connections](https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Max_used_connections)

```mysql=
show variables like "%max_connections%";
show global status like "%Max_used%";
show status like "%thread%";

mysqldump

Skip certain tables with mysqldump

migration.conf 

database=test
prev=bar.internal.foo.com
prev_bar=bar.internal.foo.com
next=bar-rds.internal.foo.com
user=foo
dump_dir=dump

db_migration.sh ``` bash=

!/bin/bash

env=$1 source migration.conf read -sp 'Please input database password: ' db_password

if [[ ! -d ${dump_dir} ]]; then mkdir ${dump_dir} else rm -rf ${dump_dir} mkdir ${dump_dir} fi

SECONDS=0 source create_db.sh source test.sh duration=$SECONDS echo "$(($duration / 60)) minutes and $(($duration % 60)) seconds elapsed." 

test.sh
``` bash=
#!/bin/bash

echo "export score db $database start: "$(date)
mysqldump -h ${env}-${prev_scoredb} \
    -u ${user} \
    -p${db_password} \
    --databases $database \
    --single-transaction \
    --order-by-primary \
    --result-file ${dump_dir}/${database}_SCORE_DB.sql
echo "export $database done: "$(date)


echo "import score db $database start: "$(date)
mysql -h ${env}-${next} -u ${user} -p${db_password} -f < ${dump_dir}/${database}_SCORE_DB.sql
echo "import $database done: "$(date)

Change character set

Cluster