SELECT benchmark(100000000,1+2);
sudo /etc/init.d/mysql stop sudo mysqld_safe --skip-grant-tables & mysql
UPDATE mysql.user SET password = password('VOTRENOUVEAUMOTDEPASSE') WHERE USER = 'root'; FLUSH privileges; exit
sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start
mysql -B -N $@ -e "SELECT DISTINCT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \ mysql $@ | \ sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
Principe : Le serveur 1 sera à la fois master pour le serveur 2 et le slave pour serveur 2 et inversement.
Remarque : Il est possible en adaptant un peu cette configuration de synchroniser que certaine bases.
server-id = 1 log_bin = /var/log/mysql/mysql-bin.log
bind-address = 127.0.0.1
GRANT replication slave ON *.* TO 'replication'@192.168.88.102 IDENTIFIED BY 'mdpslave';
server-id = 2 log_bin = /var/log/mysql/mysql-bin.log
bind-address = 127.0.0.1
master-host = 192.168.88.101 master-user = replication master-password = mdpslave master-port = 3306
CHANGE MASTER TO MASTER_HOST='192.168.88.101', MASTER_USER='replication', MASTER_PASSWORD='mdpslave';
START slave;
SHOW slave STATUS\G;
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.88.101 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 263 Relay_Log_File: mysqld-relay-bin.000003 Relay_Log_Pos: 408 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...
SHOW master STATUS;
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 106 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
GRANT replication slave ON *.* TO 'replication'@192.168.88.101 IDENTIFIED BY 'mdpslave2';
master-host = 192.168.88.102 master-user = replication master-password = mdpslave2 master-port = 3306
START slave;
SHOW slave STATUS\G;
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.88.102 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 263 Relay_Log_File: mysqld-relay-bin.000003 Relay_Log_Pos: 408 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...
SHOW master STATUS;
devrais retourner à peut près la même chose que sur serveur 1
La synchronisation est normalement opérationnel.
Si vous rencontrez une erreur du type :
Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
Voici la solution à appliquer :
stop slave;
FLUSH logs; SHOW master STATUS;
CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106; START slave;
RESET MASTER; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
mysqldump --all-databases > /tmp/mysql.sql
UNLOCK TABLES;
STOP SLAVE;
mysql < /tmp/mysql.sql
RESET SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; START SLAVE;
SHOW slave STATUS\G;
Utiliser l'outil mysqltuner qui vous dira que faire. Si il vous en parle, pour défragmenter vos tables, utiliser le script suivant :
#!/bin/bash # Get a list of all fragmented tables FRAGMENTED_TABLES="$( mysql -e "use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0;" | grep -v '^+' | sed 's,\t,.,' )" # Run Optimize on for fragment in $FRAGMENTED_TABLES; do database="$( echo $fragment | cut -d. -f1 )" table="$( echo $fragment | cut -d. -f2 )" [ $fragment != 'TABLE_SCHEMA.TABLE_NAME' ] && mysql -e "USE $database; OPTIMIZE TABLE $table;" > /dev/null 2>&1 done
Source : http://blog.barfoo.org/2008/09/19/defragmenting-all-fragmented-myisam-tables/
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION;
Le script ci-dessous automatise cela :
#!/bin/bash IN="$1" [ -z "$IN" -o ! -r "$IN" ] && echo "Usage : $0 input.sql" && exit 1 currentdb="" dbline="" endline="" nbextracted=0 headend="" function extract() { headend="$1" db="$2" f="$3" e="$4" file=db_$( echo "$db"|sed 's/[^A-Za-z\-\_]//g' ).sql echo -n "Extract DB $db (from $f to $e) to $file ... " sed -n 1,${headend}p $IN > $file sed -n ${f},${e}p $IN >> $file echo "end." let nbextracted=nbextracted+1 } IFS=" " for line in $( grep -n '\-\- Current Database:' $IN ) do nbline=$( echo "$line"|cut -d':' -f1 ) if [ ! -z "$currentdb" ] then let f=dbline-1 let e=nbline-2 extract "$headend" "$currentdb" $f $e else let headend=nbline-2 fi dbline=$nbline currentdb=$( echo "$line"|cut -d'`' -f2 ) done if [ ! -z "$currentdb" ] then let f=dbline-1 e=$( wc -l $IN|awk '{print $1}' ) extract "$headend" "$currentdb" $f $e fi echo "$nbextracted extracted DB(s) from $IN"