Tag Info

Hot answers tagged

92

Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function: mygrants() { mysql -B -N $@ -e "SELECT DISTINCT CONCAT( 'SHOW GRANTS FOR ...


11

It is entirely possible to give yourself mysql permissions without knowing SQL GRANT commands. Example : Here is to create your own user with full privileges using SQL GRANT from anywhere called superdba with a password of ClarkKent: GRANT ALL PRIVILEGES ON *.* TO superdba@'%' IDENTIFIED BY 'ClarkKent' WITH GRANT OPTION; Here is how you can do this ...


10

As far as your specific question, it's a combination of two things: By default, if a server receives a statement via replication, it will not send that same statement out to its slaves, preventing any kind of loop. This setting however, can be changed (by adding 'log-slave-updates' to the my.cnf), leading to: In replication, it sends the server-id of the ...


9

This is how i did it for both masters log-bin = mysqld-bin binlog-ignore-db=test binlog-ignore-db=information_schema binlog-ignore-db=mysql log-slave-updates replicate-ignore-db=test replicate-ignore-db=information_schema replicate-ignore-db=mysql relay-log=mysqld-relay-bin


9

There is a specific reason why what you proposed is impossible to achieve with MyISAM and InnoDB. A star topology warrants a Master being the center of the universe, not the slave. MySQL Replication was not designed to have a slave read from multiple masters simultaneously. It can only read from one master at a time. The CHANGE MASTER TO command connects a ...


8

There is no one-size-fits-all answer to scaling MySQL. A few general tips: Scale "diagonally" as long as you can, ie. keep things on a single MySQL server as long as you're still able to run on commodity hardware. That probably means 2 x quad-core CPUs, 64+ GB RAM, 8 disk RAID 10 -- or higher. The upper end of what is "commodity hardware" is getting faster ...


8

use mysqldump --opt <database> <tablename> to create a dump of your table and feed it to your new server. As you apparently have access to the remote database via TCP/IP, you simply could use mysqldump --opt --user=<youruser> --password=<yourpassword> -host <yourhost> \ <yourDB> <yourtable> | mysql -u ...


8

1 you can use maatkit's mk-heartbeat 2 you can look at result of show slave status; run on sql slave but Seconds_Behind_Master is disturbingly inaccurate at times. 3 you can hack your own solution, similar to mine - i use it both for nagios monitoring and for feeding munin charts showing 'seconds behind master'. on master server i have simple cron ...


7

There are two methods for extracting SQL Grants from a MySQL Instance METHOD #1 You can use pt-show-grants from Percona Toolkit MYSQL_CONN="-uroot -ppassword" pt-show-grants ${MYSQL_CONN} > MySQLUserGrants.sql METHOD #2 You can emulate pt-show-grants with the following MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A ...


7

The binlog on DB2 wasn't updating the slave updates. To daisy chain the replication, one must set log-slave-updates in my.conf. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates


7

i assume you use innodb as a storage engine. if so - you need to turn on bin-logging; if it's not on now - you need to restart mysql after changing my.cnf. it is the only downtime, after it you can take dump of the database with binlog position without blocking the database: mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob ...


6

Unless you take the appropriate precations this is a very real problem. Briefly, your configuration on each server needs two values set. auto_increment_increment auto_increment_offset auto_increment_offset should be set such that each master has a unique value, normally being 1 for the first, 2 for the second, etc. auto_increment_increment is the step ...


6

We use replication across datacenters in several European countries (so they aren't across the world from each other, but they are certainly not local) and it works without any problem. Replication will automatically restart if possible. If there is a problem with a query (e.g. a database is present on the master and not the slave, and a query uses it), ...


6

Welcome to the wonderful world of MySQL replication. I haven't hit your particular issue myself, but I've hit a lot of other weird problems and the proximate solution is to just resync from the master as though it's a brand new slave and be done with it.


6

Easiest thing to do would be to do a mysqldump on your linux box, and then import said dump into your local windows database. First, the mysqldump (docs: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html) mysqldump -u [username] -p [any other options desired] [database name] > dump.sql The above creates a file called "dump.sql" in the directory ...


6

MySQL 5.5 has semi-synchronous replication. Basically it guarantees that at least one slave has received the transaction before it is commited. http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html


5

You can mysqldump the 'mysql' database and import to the new one; a flush_privileges or restart will be required and you'll definitely want to back up the existing mysq db first. To avoid removing your existing privileges, make sure to append rather than replace rows in the privilege tables (db, columns_priv, host, func, etc.).


5

Or, utilize percona-toolkit (former maatkit) and use pt-show-grants (or mt-show-grants) for that purpose. No need for cumbersome scripts and/or stored procedures.


5

In MySQL replication, a slave "syncs" its data from the master's transaction log, not directly from the database. The slave stores the last position in that transaction log, so as long as you have not touched that position number on the slave, then as soon as it becomes active again, it will resume executing transactions from that position it left off at. ...


5

If you look at the MySQL documentation, you will notice the following line: Any long option that may be given on the command line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the --help option. Further details clarify the above statement: The syntax for ...


5

What to try to fix your problem: You should remove master.info on slave first and restart mysql issue CHANGE MASTER TO MASTER_HOST='XX.XX.XX.XX', MASTER_USER='repl', MASTER_PASSWORD='slavepass'; do mysqldump with '--flush-logs' option on master 'mysql -u user -p < dump.sql' on slave 'show slave status\G' on slave to ensure that it is properly ...


5

The topology you're describing is called "circular replication". You're basically setting up a ring in which each node acts as a master to the following one. The authors of High Performance MySQL advise against this topology, with the following reasons: it depends on each node being available, therefore increasing your failure probability. if you remove a ...


4

The straight-forward course of action is to wipe out the data on the slave and start fresh with a new copy of the master. The master can be copied by using mysqldump or through something like rsync. Depending on how large your database is, you may experience downtime. This will always work. There's a chance that if you clear space off the slave drive you ...


4

No, your assuming is wrong. You must take a data snapshot and configure the slave with the replication coordinates from the master. Take a look at this for more details.


4

In MySQL command line, give command SHOW SLAVE STATUS\G and see the Seconds_behind_ master value. Or if you want to script that somehow, the one-liner below returns how many seconds behind master the slave currently is. mysql -u your_user -e 'SHOW SLAVE STATUS\G' | grep "Seconds_Behi" | awk '{ print $2; }'


4

I've had no problems with replicating the mysql database, but then again, my infrastructure lends to an additional level of security with firewalls and proxy appliances where it's impossible for anyone other than the infrastructure folks to even connect on any of the ports MySQL uses. It does add an extra level of convenience knowing that you only have to ...


4

A wordpress instance with a decent cache (Wordpress SuperCache comes to mind) should help a lot in serving a very heavy wordpress site. But the first thing you need to do is identify where your bottleneck is. If your database engine is fairly light on, but there's a lot of server-side post-processing of data, then simply scaling out your web servers may ...


4

Another option besides using rsync is to configure mysql replication with each of the normal databases as masters, and the ones in your office as a slave for each master. You can read mysql's documentation here.. If you want to keep with the scp/rsync style backup, you can maybe add compression to the backup with bzip, or some other method. There is also ...


4

With regard to making databases, there are still bug reports on using CREATE DATABASE with row-based replication. This report is closed, but the bug appeared again in MySQL 5.1.47 This report is based on MySQL Cluster (NDB storage engine) This report is based on replicate-wild-ignore-table still replicating. Row-based replication causes binary logs to grow ...


4

It is not possibile, Mysql supports only multi master-master circular replication. This article describes this replication very well.



Only top voted, non community-wiki answers of a minimum length are eligible