Tag Info

Hot answers tagged

102

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

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 ...


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

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 ...


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

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 ...


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

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 ...


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


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

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

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

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


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

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

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


5

Richard Bronosky's answer was extremely useful for me. Many thanks!!! Here is a small variation which was useful for me. It is helpful for transfering users e.g. between two Ubuntu installations running phpmyadmin. Just dump privileges for all users apart from root, phpmyadmin and debian-sys-maint. The code is then mygrants() { mysql -B -N $@ -e "SELECT ...


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

Yes, it is possible :) This is called "Master with Relay Slave" replication and there a lot of documents about it on the net. I'd recommend you to take a look at the official documentation here. Btw, also take a look at these slides. They've some hints about replication topologies. Hope this helps.


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

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 ...


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 ...


4

Replication changed dramatically in MySQL 5.1. In 5.0 only Statement Based Replication was used. You now have the option to do Row Based Replication or Mixed Based Replication. This will greatly affect how you replicate over a WAN. If you have the ability to either: A) Do IP take over (if your servers are geographically separated this is not likely) B) Make ...


4

pQd has it, checking 'show slave status' is the easiest way. Regarding Seconds_behind_master being inaccurate, I wanted to mention that the value is the difference in the timestamp for the statement being read out of the relay log by the slave SQL thread; it's not related to an estimate of how long it will take to catch up. For instance, a single ...


4

Due to a reasonably long delay with no answers, I have found out by trial and error. The answer is : Yes, I can run mysqld on top of glusterfs. I set this up on Ubuntu and briefly here are the steps: Install Ubuntu Install and configure glusterfs Install mysql-server Configure /etc/mysql/my.cnf to set datadir /mnt/glusterfs instead of /var/lib/mysql Set ...


4

Statement-based replication is the fastest and most compact, but in some circumstances it can produce different (non-deterministic) results on slaves than on the master, resulting in inconsistency. An example might be: UPDATE mytable SET a = a + 1 LIMIT 1; There's no way to guarantee which row will get updated as there is no sort order on it and order on ...


4

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


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 ...



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