mysql

การทำ replication from phpmyadmin

http://www.freelancethai.net/forum/index.php?topic=79.0

$ rsync -a ./database_dump.sql user@192.0.2.1:/tmp/

:~$> mysqldump -u root -p db1 > dump.sql :~$> mysqladmin -u root -p create db2 :~$> mysql -u root -p db2 < dump.sql

mysqldump -u kri -p123456 chat | mysql -u kri -p123456 chat2

recovery myql database

service mysql stop

copy

A MySQL MyISAM table is the combination of three files:

- The FRM file is the table definition.
- The MYD file is where the actual data is stored.
- The MYI file is where the indexes created on the table are stored.

Step 1. Shutdown Mysql server

Step 2. Copy database in your database folder (in linux, the default location is /var/lib/mysql). Keep same name of the database, and same name of database in mysql mode.

sudo cp -rf /mnt/ubuntu_426/var/lib/mysql/database1 /var/lib/mysql/

Step 3: Change own and change mode the folder:

sudo chown -R mysql:mysql /var/lib/mysql/database1 sudo chmod -R 660 /var/lib/mysql/database1 sudo chown mysql:mysql /var/lib/mysql/database1 sudo chmod 700 /var/lib/mysql/database1

Step 4: Copy ibdata1 in your database folder

sudo cp /mnt/ubuntu_426/var/lib/mysql/ibdata1 /var/lib/mysql/ sudo chown mysql:mysql /var/lib/mysql/ibdata1

Step 5: copy ib_logfile0 and ib_logfile1 files in your database folder.

sudo cp /mnt/ubuntu_426/var/lib/mysql/ib_logfile0 /var/lib/mysql/ sudo cp /mnt/ubuntu_426/var/lib/mysql/ib_logfile1 /var/lib/mysql/

Remember change own and change root of those files:

sudo chown -R mysql:mysql /var/lib/mysql/ib_logfile0 sudo chown -R mysql:mysql /var/lib/mysql/ib_logfile1

sudo chown -R mysql:mysql /var/lib/mysql

replicate mysql

yum install mariadb-server

systemctl status mariadb

systemctl enable mariadb

systemctl start mariadb

systemctl status mariadb

yum install net-tools

netstat -nltp

firewall-cmd --get-service |grep mysql --color

firewall-cmd --permanent --add-service-mysql

firewall-cmd --reload

firewall7-cmd --list7-all

mysql_secure_installation

mysql 7-uroot -p

show schemas;

create database db1;

create datebase db2;

use db1;

create table dbtb1 (name varchar(10));

use db2;

create table dbtb2(name varchar(10));

show schemas;

insert into db1.dbtb1 values('aaa');

insert into db2.dbtb2 values('bbb');

vi /etc/my.cnf

at [mysqld]

add line: server-id=1

:log-bin=mysql-bin

pwd

answer = /root

vi .my.cnf

[client]

user-root

password-xxxxxx

mysql

grant replication slave on *.* to repl@'192.168.x.x' identified by 'repl';

flush privileges;

flush tables with read lock;

systemctl restart mariadb

mysql

show master status\g;

flush tables with read lock;

show schemas;

mysqldump db1 > db1.sql

cenvm02 = com2

scp db1.sql cenvm02:/root/

change to com2

yum install mariadb-server

systemctl start mariadb

firewall-cmd --permanent -- add-service-mysql

firewall-cmd --reload

systemctl start mariadb

system status mariadb

mysql_secure_installation

vi .my.cnf

[client]

user-root

password-admin

mysql -e "show schemas"

mysql -e "create database db1"

mysql db1 < db1.sql

vi ./my.cnf

[mysqld]

server-id=2

replicate-wild-do-table=db1.%

systemctl restart mariadb

mysql -uxxxxx -pxxxxxx

update tikisvn3.krieng2 set BRDATE = BRDATE - INTERVAL 543 YEAR;

sudo apt-get install samba

sudo apt-get install libmyodbc

In order to "zip" a directory, the correct command would be

tar -cvzf archive.tar.gz directory/

This will tell tar to c (create) an archive from the files in directory (tar is recursive by default), compress it using the z (gzip) algorithm, store the output as a f (file) named archive.tar.gz, and v (verbosely) list all the files it adds to the archive.

To decompress and unpack the archive into the current directory you would use

tar -xvzf archive.tar.gz

mysqldump -h localhost -u root -pxxxxxx tikisvn3 > tikisvn3kri.sql

mysql -u root -pxxxxxxx tikisvn3< tikisvn3kri.sql

GRANT ALL PRIVILEGES ON *.* TO 'root'@'(my_host)' IDENTIFIED BY '(my_password)';

GRANT ALL PRIVILEGES ON *.* TO 'hospital'@'%' IDENTIFIED BY '(my_password)';

FLUSH PRIVILEGES;

in /etc/mysql/my.cnf add line at the end

sudo nano /etc/mysql/my.cnf

[mysqld]

bind-address = 0.0.0.0

sudo service mysql restart

add user host %

For Backup and Restore MariaDB Data, it's possible to run with [mariabackup].

[1]

Install Backup tool.

root@www:~#

apt -y install mariadb-backup

[2]

Run Backup.

For example, get backup under [/home/mariadb_backup].

root@www:~#

mkdir /home/mariadb_backup

root@www:~#

mariabackup --backup --target-dir /home/mariadb_backup -u root

.....

[00] 2020-05-09 16:47:33 Executing UNLOCK TABLES

[00] 2020-05-09 16:47:33 All tables unlocked

[00] 2020-05-09 16:47:33 Copying ib_buffer_pool to /home/mariadb_backup/ib_buffer_pool

[00] 2020-05-09 16:47:33 ...done

[00] 2020-05-09 16:47:33 Backup created in directory '/home/mariadb_backup/'

[00] 2020-05-09 16:47:33 Writing backup-my.cnf

[00] 2020-05-09 16:47:33 ...done

[00] 2020-05-09 16:47:33 Writing xtrabackup_info

[00] 2020-05-09 16:47:33 ...done

[00] 2020-05-09 16:47:33 Redo log (from LSN 1631383 to 1631392) was copied.

[00] 2020-05-09 16:47:33 completed OK!

root@www:~#

ll /home/mariadb_backup

total 12348

drwxr-xr-x 5 root root 4096 May 9 16:47 ./

drwxr-xr-x 4 root root 4096 May 9 16:47 ../

-rw-r----- 1 root root 16384 May 9 16:47 aria_log.00000001

-rw-r----- 1 root root 52 May 9 16:47 aria_log_control

-rw-r----- 1 root root 324 May 9 16:47 backup-my.cnf

-rw-r----- 1 root root 1004 May 9 16:47 ib_buffer_pool

-rw-r----- 1 root root 12582912 May 9 16:47 ibdata1

-rw-r----- 1 root root 2560 May 9 16:47 ib_logfile0

drwx------ 2 root root 4096 May 9 16:47 mysql/

drwx------ 2 root root 4096 May 9 16:47 performance_schema/

drwx------ 2 root root 4096 May 9 16:47 test_database/

-rw-r----- 1 root root 77 May 9 16:47 xtrabackup_checkpoints

-rw-r----- 1 root root 443 May 9 16:47 xtrabackup_info

[3]

For restoring data from backup on another host, run like follows.

Before restoring, transfer backup data to the target host with [rsync] or [scp] and so on.

# stop MariaDB and remove existing data

root@node01:~#

systemctl stop mariadb

root@node01:~#

rm -rf /var/lib/mysql/*

# transfered backup data

root@node01:~#

ll mariadb_backup.tar.gz

-rw-r--r-- 1 root root 214745 May 9 16:59 mariadb_backup.tar.gz

root@node01:~#

tar zxvf mariadb_backup.tar.gz

# run prepare task before restore task (OK if [completed OK])

root@node01:~#

mariabackup --prepare --target-dir /root/mariadb_backup

mariabackup based on MariaDB server 10.3.22-MariaDB debian-linux-gnu (x86_64)

[00] 2020-05-09 17:03:53 cd to /root/mariadb_backup/

[00] 2020-05-09 17:03:53 This target seems to be not prepared yet.

[00] 2020-05-09 17:03:53 mariabackup: using the following InnoDB configuration for recovery:

[00] 2020-05-09 17:03:53 innodb_data_home_dir = .

[00] 2020-05-09 17:03:53 innodb_data_file_path = ibdata1:12M:autoextend

[00] 2020-05-09 17:03:53 innodb_log_group_home_dir = .

[00] 2020-05-09 17:03:53 InnoDB: Using Linux native AIO

[00] 2020-05-09 17:03:53 Starting InnoDB instance for recovery.

[00] 2020-05-09 17:03:53 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

2020-05-09 17:03:53 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2020-05-09 17:03:53 0 [Note] InnoDB: Uses event mutexes

2020-05-09 17:03:53 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2020-05-09 17:03:53 0 [Note] InnoDB: Number of pools: 1

2020-05-09 17:03:53 0 [Note] InnoDB: Using SSE2 crc32 instructions

2020-05-09 17:03:53 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M

2020-05-09 17:03:53 0 [Note] InnoDB: Completed initialization of buffer pool

2020-05-09 17:03:53 0 [Note] InnoDB: page_cleaner coordinator priority: -20

2020-05-09 17:03:53 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1631383

[00] 2020-05-09 17:03:54 Last binlog file , position 0

[00] 2020-05-09 17:03:54 completed OK!

# run restore

root@node01:~#

mariabackup --copy-back --target-dir /root/mariadb_backup

mariabackup based on MariaDB server 10.3.22-MariaDB debian-linux-gnu (x86_64)

[01] 2020-05-09 17:04:31 Copying ibdata1 to /var/lib/mysql/ibdata1