- discuss - lists.mariadb.org

Re: [Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 19 Oct '10

19 Oct '10

Davi, Sergei (and everyone else), Thanks for the great feedback. It got me looking at the capabilities of MySQL 5.5. It's POSSIBLE that the new syntax: FLUSH TABLES t1, t2, t3, ... , tn WITH READ LOCK is PRECISELY the functionality we need! Our (perl) migration script MIGHT (for example) do the following: Issue the following command (implemented by me ...): 1. START BINLOG FOR DATABASE <db>; (for each database a given user owns. [Hmmm... Maybe my implementation allows multiple <db> arguments. I need to check.] Each database would start binlogging to its own file. [We will configure--in my.cnf--the 1-database-per-file binlogs to be stored on a very fast solid-state drive.]) Then the script creates a list of all tables in all the user's databases being migrated to another server and issues the command (new to MySQL 5.5): 2. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK; (where the tables t1, t2, ..., tn are the tables being migrated.) Then issue the following command (implemented by me ...): 3. SHOW DATABASE BINLOG STATUS; which shows--for each database being binlogged to its own file: a. the database name b. whether binlogging is active c. the name of the current binlog file d. the binlog position in that file. The script captures the coordinates, then 4. Creates a copy of the database(s) using mysqldump or scp/tar/gzip, etc. (MyISAM databases will probably just be copied "raw," because it is much faster. Innodb will probably have to use mysqldump or something similar.) 5. UNLOCK TABLES: On the new server, the copied/mysqldump'ed files will be used to bring up the mysql on the new server. At some future time--hopefully SOON--the tables could be locked again. For any where the coordinates have changed: The binlog file(s) would be copied to the new server, "played" by mysqlbinlog, and the new server would be brought online on the new box and taken offline on the old. When the migration is determined to be complete, issue this command (implemented by me) on the old box: 6. STOP BINLOG FOR DATABASE <db>; for all the user's databases ... and the migration is done. QUESTIONS/ASSUMPTIONS: 1. In playing with the new FLUSH TABLES t1, t2, ..., tn command, I've noticed that if the connection drops, the locks are all unlocked. ... So our scripts will have to make sure the connection stays up. (ANY SUGGESTIONS?) 2. The UNLOCK TABLES command unlocks all the tables locked earlier in the same session. (So it doesn't need the variable argument list of tables to unlock ...) A post on dev.mysql.com shows the following possible sequence of commands: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; SYSTEM xfs_freeze -f /var/lib/mysql; SYSTEM YOUR_SCRIPT_TO_CREATE_SNAPSHOT.sh; SYSTEM xfs_freeze -u /var/lib/mysql; UNLOCK TABLES; EXIT; (The FLUSH TABLES command would correspond to #2 above. SHOW MASTER STATUS would correspond to #3 above. UNLOCK TABLES would correspond to #5 above. The SYSTEM commands would have to be replaced by our own commands/scripts.) Would running (sub-)scripts from SYSTEM commands inside MySQL be sufficient to hold the locks? BUG IDENTIFIED. I noticed the following mysqld-crashing bug--in 5.5.6 rc as well as the one I modified: 1. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK; 2. FLUSH TABLES; The second FLUSH TABLES (with no added tokens) crashes at mdl.cc:1563 : DBUG_ASSERT(mdl_request->type != MDL_EXCLUSIVE || is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE)); because is_lock_owner() returns NULL when FLUSH TABLES is called immediately after FLUSH TABLES [with additional args]; QUESTION: I read that Larry Ellison stated (at a conference) that 5.5 would be out by year end. How likely is that? Our company generally waits for some point releases before changing our customer boxes to the next release. Should I try to back port those locks to 5.1 in the interim? ... or leave well enough alone? Thanks in advance for your feedback/help/advice!! We.don Whipple On Mon, Oct 18, 2010 at 12:48 PM, Davi Arnaut <davi.arnaut(a)oracle.com> wrote: > On 10/18/10 3:16 PM, Weldon Whipple wrote: >> >> Well, I finished a working prototype that does everything except the >> locking and unlocking of databases. Someone mentioned that it's now >> possible to lock individual tables. Would you--anyone--be willing to >> point me toward places in the 5.5 code that use that >> locking/unlocking? >> > > Do you want to lock the database, or individual tables, or both? Anyway, > take a look at mysql_rm_db as the locking is all very similar. Roughly, the > algorithm of a DROP DATABASE is: > > // Protect against other schema operations > lock_schema_name("db1"); > // Build list of tables in the db1 directory > table_list = .. > // Initialize metadata lock request > table_list->mdl_request.init(MDL_key::TABLE, > table_list->db, > table_list->table_name, MDL_EXCLUSIVE); > // Lock tables > lock_table_names(thd, tables, NULL ...) > // Remove from cache and drop the table... > > The best thing at this point is to study (and debug) a similar statement and > understand how metadata locks work (mdl prefix) or how tables are > open/locked. > > Regards, > > Davi > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=weldon@whipple.org > >

1 0

Re: [Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 18 Oct '10

18 Oct '10

Davi, Thanks for the pointers! I'd like to lock an entire database. (I guess I can iterate through the tables in database if that's the only way. [I suppose there might be possible locking issues to deal with ...]) I'll look at the source you recommend ... Weldon weldon(a)whipple.org On Mon, Oct 18, 2010 at 12:48 PM, Davi Arnaut <davi.arnaut(a)oracle.com> wrote: > On 10/18/10 3:16 PM, Weldon Whipple wrote: >> >> Well, I finished a working prototype that does everything except the >> locking and unlocking of databases. Someone mentioned that it's now >> possible to lock individual tables. Would you--anyone--be willing to >> point me toward places in the 5.5 code that use that >> locking/unlocking? >> > > Do you want to lock the database, or individual tables, or both? Anyway, > take a look at mysql_rm_db as the locking is all very similar. Roughly, the > algorithm of a DROP DATABASE is: > > // Protect against other schema operations > lock_schema_name("db1"); > // Build list of tables in the db1 directory > table_list = .. > // Initialize metadata lock request > table_list->mdl_request.init(MDL_key::TABLE, > table_list->db, > table_list->table_name, MDL_EXCLUSIVE); > // Lock tables > lock_table_names(thd, tables, NULL ...) > // Remove from cache and drop the table... > > The best thing at this point is to study (and debug) a similar statement and > understand how metadata locks work (mdl prefix) or how tables are > open/locked. > > Regards, > > Davi > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=weldon@whipple.org > >

1 0

[Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 18 Oct '10

18 Oct '10

Thanks, Sergei and everyone else, for your feedback/ideas! Well, I finished a working prototype that does everything except the locking and unlocking of databases. Someone mentioned that it's now possible to lock individual tables. Would you--anyone--be willing to point me toward places in the 5.5 code that use that locking/unlocking? I'm interested in sharing the changes with the community if there is any interest. (Any suggestions about whether I should prefer launchpad versus forge.mysql.com or some other place to share? [I once owned a project on SourceForge--I think it's still there, but not very active ...]. :-) A few comments in response to your comments: On Thu, Sep 16, 2010 at 6:34 AM, Sergei Golubchik <serg(a)askmonty.org> wrote: > Hi, Weldon! > > On Sep 15, Weldon Whipple wrote: >> >> > See where mysql_bin_log.write is called. >> >> Excellent information! (I was hoping it might be that easy ???). If I >> succeed in making a DB_BIN_LOG class derive from MYSQL_BIN_LOG (with an >> instance of DB_BIN_LOG for each database that is currently binlogging), >> I could--in theory, at least--just call something like >> db_binlog->write () at those places. > > Better make this write method virtual and implement it in your derived > class. I did implement it as a derived class, but it was a bit more work than just reimplementing the ::write() method. (I use separate instances of DB_BINLOG--the derived class--for each database that is binlogging to its separate binlog file [sequence].) I had to have a few additional methods (extremely small) to set up for opening files named <database>-bin.<seq#>, <database>-bin.index, etc. I also maintain a db_binlog.info file that remembers the databases being binlogged across restarts. One major change was in the way the global MYSQL_BIN_LOG class is referenced. In the original code, it comes into existence at the top of binlog.cc. Before I started making my modifications, I changed MYSQL_BIN_LOG to be a pointer to MYSQL_BIN_LOG, declared in mysqld.cc (and extern'ed in mysqld.h). At the point in mysqld.cc where mysql_bin_log is first referenced I "new" the global instance of MYSQL_BIN_LOG*. I went through all the files that reference mysql_bin_log and made the necessary changes so that mysql_bin_log works as a pointer. (Example: Change "mysql_bin_log.is_active()" to "mysql_bin_log->is_active().) Also, before, referencing mysql_bin_log*, I always make sure it is non-NULL ... Then I made my changes for 1 db-per-file binlogging, using #ifdef HAVE_DB_BINLOG/#endif blocks to allow building with or without 1-db-per-file binlogging. As I'm typing this post, I'm running mysql-test-run.pl on a build without HAVE_DB_BINLOG defined and it seems to be passing all the tests. I need to write additional test cases for when HAVE_DB_BINLOG is defined ... Thanks in advance for ideas/suggestions (as well as for the ideas in the past). Weldon Whipple weldon(a)whipple.org (I'll leave the conversations below to help refresh your memory on this thread's past.) > >> >> Questions I Have. >> >> ---------------- >> >> >> >> 1. I notice that binlog is in the list of engines and plugins >> >> (displayed by SHOW ...). The bottom of log.cc has >> >> "mysql_declare_plugin(binlog)". Do I need one for db_plugin? >> >> (probably not?) >> > >> > depends. >> > binlog needs to be declated a plugin, to be able to pretent being a >> > storage engine, to be able to force two phase commit (2PC). >> > >> > 2PC is used to commit or rollback a transaction atomically in more >> > than one engine. And MySQL only uses 2PC when a transaction spans >> > more than one engine. But we want a transaction to be committed in >> > the engine and written to a binlog - and it should be done >> > atomically too. So, binlog pretends to be an engine, pretends to >> > participate in a transaction, which forces MySQL to use 2PC which >> > can guarantee that a transaction is either committed and written to >> > a binlog - or not committed and not written. Reliably, even if MySQL >> > crashes in the middle. >> > >> > So, if you don't use innodb, or don't use >> > innodb_flush_log_at_trx_commit=1 or don't use sync_binlog=1 you >> > probably don't need to support 2PC as your setup is not crash proof >> > anyway. >> > >> > If you care about innodb<->binlog consistency in the presence of >> > crashes, you should support 2PC in your binlog, as explained above. >> >> We DO have a small percentage of innodb databases. If 2PC commits >> (rolls back, etc.) atomically to more than one engine, does that mean >> TWO (only)? ... or could it happen for three (specifically, innodb, >> MYSQL_BIN_LOG's binlog, and DB_BIN_LOG's binlog)? > > See http://en.wikipedia.org/wiki/Two-phase_commit_protocol > > It's two phases, not two engines, any number of engines can participate > in a transaction. > > But! All of them but one have to support two-phase commit. Binlog does > not support it. Apparently, DB_BIN_LOG will not support it either - > adding two-phase commit support to binlog will surely be beyond the > scope of your work. > > It means, you cannot have InnoDB, binlog, and DB_BIN_LOG to participate > in a two-phase commit. But you can disable MYSQL_BIN_LOG altogether and > only use DB_BIN_LOG - in that case you can have a two-phase commit > between DB_BIN_LOG and InnoDB. > >> The two kinds of binlog files would have the same format (except that >> one is for only one database). Are they considered two storage >> engines/plugins? > > If you want to write to both - yes. A transaction will need to be > written to both independently, so you want them to be in sync, and MySQL > uses 2PC to synchronize different data storages. > > You can solve that by > > 1) not using MYSQL_BIN_LOG. In this case there's only InnoDB and > DB_BIN_LOG to keep in sync, and InnoDB can do 2PC. > > 2) not making DB_BIN_LOG independent from MYSQL_BIN_LOG. For example, > everything is written to binlog only. Then you have a background > thread (or even a separate process) that reads binlog and writes your > per-db binlogs. In that case you have only InnoDB and MYSQL_BIN_LOG > to keep in sync, and MySQL already does that. Your background thread > is only catching up, it is allowed to fall behind. > >> (I notice that the "mysql_declare_plugin(binlog)" declaration is at >> the bottom of log.cc. Most of the other storage engines' corresponding >> declarations are in files in the storage subdirectories. If I were to >> need a "mysql_declare_plugin(db_binlog)" declaration, could it also go >> at the bottom of log.cc? ... Or (since a DB_BIN_LOG is AKO [a kind of] >> MYSQL_BIN_LOG [in the OO sense]), are MYSQL_BIN_LOGs and DB_BIN_LOGs >> both kinds of binlog plugins ...? > > if you want to have two plugins in one file you still need to use one > mysql_declare_plugin(), not two. See example at the end of > http://www.packtpub.com/article/look-inside-mysql-daemon-plugin > > Regards, > Sergei >

1 0

[Maria-discuss] WL #47
by Oleg Tsarev 13 Oct '10

13 Oct '10

Hello Maria! https://code.launchpad.net/~percona-dev/percona-server/release-5.1.49-12-rnt When i build this branch: cd Percona-Server/ && ./BUILD/autorun.sh && ./configure --prefix=/usr/local/mysql --exec-prefix=/usr/local/mysql --bindir=/usr/local/mysql/bin --sbindir=/usr/local/mysql/bin --sysconfdir=/etc --datadir=/usr/local/mysql/share --includedir=/usr/local/mysql/include --libdir=/usr/local/mysql/lib --libexecdir=/usr/local/mysql/bin --with-client-ldflags=-all-static --localstatedir=/var/lib/mysql --sharedstatedir=/usr/local/mysql/com --mandir=/usr/local/mysql/man --infodir=/usr/local/mysql/share/info --enable-shared --enable-static --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --with-pic --with-extra-charsets=complex --with-ssl --enable-thread-safe-client --with-readline --without-plugin-ndbcluster --without-plugin-maria --without-plugin-innobase --with-plugin-innodb_plugin --with-plugin-partition --with-plugin-csv --with-plugin-archive --with-plugin-blackhole --with-plugin-federated --without-embedded-server --without-bench --with-zlib-dir=bundled --with-big-tables && make -j3 And then run tests: cd mysql-test && ./mysql-test-run main.ctype_cp932_binlog_stm I get test error: worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 main.ctype_cp932_binlog_stm [ fail ] Found warnings/errors in server log file! Test ended at 2010-10-13 08:02:25 line 101013 18:02:25 [ERROR] Error in Log_event::read_log_event(): 'Event too big', data_len: 50724864, event_type: 0 ^ Found warnings in /mnt/data/home/oleg.tsarev/12687/Percona-Server/mysql-test/var/log/mysqld.1.err ok - saving '/mnt/data/home/oleg.tsarev/12687/Percona-Server/mysql-test/var/log/main.ctype_cp932_binlog_stm/' to '/mnt/data/home/oleg.tsarev/12687/Percona-Server/mysql-test/var/log/main.ctype_cp932_binlog_stm/' ------------------------------------------------------------ The servers were restarted 0 times Spent 0.000 of 1 seconds executing testcases mysql-test-run: WARNING: Got errors/warnings while running tests, please examine '/mnt/data/home/oleg.tsarev/12687/Percona-Server/mysql-test/var/log/warnings' for details. Completed: Failed 1/1 tests, 0.00% were successful. Failing test(s): main.ctype_cp932_binlog_stm I found, what reason of this bug - our patch wl47.patch. I try to fix this bug, but i know replication not enough for this. Do you know about this trouble with WL #47? Best regards, Oleg --- Oleg Tsarev, Software Engineer, Percona Inc.

1 0

[Maria-discuss] improving the thread/connection pool
by MARK CALLAGHAN 11 Oct '10

11 Oct '10

I think more work needs to be done on the MariaDB thread/connection pool. Does a worklog exist for that? The problems are: * multi-core performance * stalled threads stall the pool This is based on my review and testing of the feature in MySQL 6.0 and quickly browsing the code in MariaDB. I also fixed multi-core performance as a proof of concept but don't have access to that patch. The multi-core performance problem occurs because only one event_base instance is used (see sql/scheduler.cc). Note that memcache uses one per CPU core. This is a big source of mutex contention. An additional problem is that EPOLLONESHOT is not used. I don't know if libevent supports that yet. Were it used one less interaction with libevent would be needed per query. I reported the performance issue at http://bugs.mysql.com/bug.php?id=42288 An interesting decision when there are multiple event_base instances is whether the mapping between an event_base instance and connections is fixed. If it is fixed then there is less libevent work to do per-query and there will be less mutex contention. But if connections cannot migrate between event_base instances per query then there will be cases where too many pending queries wait for one and no pending queries exist for others. I think that stalls are another problem to fix. When a user connection blocks on a row lock wait or another internal locks (and calls THD::enter_cond), then this is an opportunity to count this connection as not running and schedule another thread. This isn't a trivial problem to fix because it might require a new thread to be created. There is also the issue of what to do when another connection has been scheduled. In that case does the thread that just became runnable get to run immediately? If it was just granted a row lock or other lock then we want it to run right away. But that might cause X+1 threads to be running in the thread pool when the limit is X. The difference between X and X+1 is small. But this could lead to 2*X threads running. What is the limit? -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-discuss] MariaDB 5.2.2-gamma
by Daniel Bartholomew 29 Sep '10

29 Sep '10

All, MariaDB 5.2.2-gamma source and binary tarballs, and packages for various operating systems and Linux distributions are now available for download: http://askmonty.org/wiki/MariaDB:Download:MariaDB_5.2.2-gamma See also: Release Notes: http://kb.askmonty.org/v/mariadb-522-release-notes Changelog: http://kb.askmonty.org/v/mariadb-522-changelog Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

3 3

[Maria-discuss] MariaDB Knowledgebase typo
by Yousif Masoud 16 Sep '10

16 Sep '10

Hi, I'd like to thank everyone working on this project. MariaDB is currently installed on one of my Dev environments, I am looking forward to switching to it. I noticed there was a minor typo on: http://kb.askmonty.org/v/compiling-with-the-innodb-plugin-from-oracle First sentence reads: Normally MariaDB comes with XtraDB <http://kb.askmonty.org/v/xtradb-storage-engine>, an enhanced version **if** the InnoDB plugin that comes from Oracle. I believe it should be: Normally MariaDB comes with XtraDB <http://kb.askmonty.org/v/xtradb-storage-engine>, an enhanced version **of** the InnoDB plugin that comes from Oracle. Many thanks, Yousif PS. I don't know if there is a formal method for reporting this. Please accept my apologies if this is not the best way to do it.

2 1

Re: [Maria-discuss] Seeking command syntax feedback (Still Re: Per-db binlogging)
by Weldon Whipple 16 Sep '10

16 Sep '10

On Wed, Sep 15, 2010 at 2:00 PM, Sergei Golubchik <serg(a)askmonty.org> wrote: > Hi, Weldon! Hi, Sergei! > > On Sep 15, Weldon Whipple wrote: <snip/> >> 2. FLUSH TABLES WITH READ LOCK FOR DATABASE dbname; >> >> (I just added an optional 'FOR DATABASE dbname' to the already >> existing command--which still works, BTW. Is this a misuse of the word >> "FOR"?) >> This is the command that locks the tables before someone (some >> program) dumps/copies the tables that will be moved to another server. >> It flushes and locks only one database. > > This would be difficult to implement. > > FLUSH TABLES WITH READ LOCK is not implemented in a way that can be > generalized to support per-database locks :( I had guessed as much. (The method in my DB_BINLOG_MGR method that implements that functionality consists of a mutex lock, a comment about some sort of miracle happening, then a mutex unlock.) I wonder if there might be a way of inhibiting some user database (temporarily) from changing ... Alternately, I suppose we could do a global FLUSH TABLE WITH READ LOCK/ UNLOCK TABLES. Or ... (???) > Besides, I personally wouldn't worry much about the syntax. It's > something you can easily change anytime - e.g. replace BINLOG with > BINLOGGING or FOR with something else. Yeah, every time I write a new prototype I tweak the syntax a bit. At the moment I'm just trying to settle on an implementation that would work for our company (and still be of some sort of general use). > > Regards, > Sergei > Thanks, Weldon

1 0

Re: [Maria-discuss] Per-db binlogging
by MARK CALLAGHAN 16 Sep '10

16 Sep '10

+maria-discuss as they are working in replication too +robert.hodges as tungsten replicator has features that could be used here I am very interested in a subset of this. I want one SQL thread per database on a slave (stream per database on the slave). However I don't need to split the binlog on the master to get that and writing a binlog file per database on the master might complicate the sync_binlog option that keeps the binlog and InnoDB in sync during crash recovery. How will this work in that case? Note that a stream (SQL slave thread) per database on the slave will break rpl_transaction_enabled. Some of us rely on that to keep slaves consistent during crash recovery. I assume you don't use that feature. MySQL has worklogs to use db tables in place of relay-log.info and when that code is production ready, then this is not a problem. On Mon, Sep 13, 2010 at 1:52 PM, Weldon Whipple <weldon(a)whipple.org> wrote: > Greetings! > > My employer has asked me to implement per-DB binlogging for > MySQL. I've been working on prototypes for 3-4 weeks now, and decided > it's time to post to this list to ask for comments, suggestions, > condolences, etc. (I've been a "lurker" for quite awhile. I hope this > post isn't too far off the mark) The assignment is *only* for master-side > binlogging. (It doesn't require a slave/replication-side implementation.) > > > Why I Want to Do It. > ------------------- > > I work for an ISP that hosts (last I heard--I HOPE I'm not lying) over > 2 million domains. Customers are allowed to have (almost) unlimited > MySQL databases. Migrating an account from one physical server to > another is a daunting task. (A typical server has easily 2000+ > [sometimes far more] databases.) Global mysqld binlogging isn't > feasible when we want to migrate (for example) a single account to > another server. > > Our proposed scenario goes something like this: > > 1. START USER BINLOG FOR DATABASE <db> > 2. FLUSH USER TABLES WITH READ LOCK FOR DATABASE <db> > 3. SHOW USER BINLOG STATUS [WHERE DATABASE LIKE <pattern>] > > SHOW output might look something like: > ================================= > mysql> show user binlog status; > +----------------------+----------+---------------------------------+------------+ > | Database | User | Binlog_file | > Binlog_pos | > +----------------------+----------+---------------------------------+------------+ > | booklibs | wwhipple | booklibs-bin.000003 | > 248 | > | charmins_gravestones | charmins | charmins_gravestones-bin.000001 | > 4 | > +----------------------+----------+---------------------------------+------------+ > 2 rows in set (0.01 sec) > > The "where" clause also works: > > mysql> show user binlog status where user like 'charmins'; > +----------------------+----------+---------------------------------+------------+ > | Database | User | Binlog_file | > Binlog_pos | > +----------------------+----------+---------------------------------+------------+ > | charmins_gravestones | charmins | charmins_gravestones-bin.000001 | > 4 | > +----------------------+----------+---------------------------------+------------+ > 1 row in set (0.01 sec) > ================================= > > At this point the migration tools note the binlog coordinates and > start copying the databases to the new server. (For myisam tables, the > tools writers want to just copy [scp?] the files. For innodb, they > will use something like mysqldump.) > > (Note: We might want to "lose" the "User" column in the above table] > in our recent designs.) > > Then the sequence of MySQL commands resumes: > > 4. UNLOCK USER BINLOG FOR DATABASE <db> > > Finally, when migration is complete: > > 5. STOP USER BINLOG FOR DATABASE <db> > > A high percentage of users (infrequent bloggers, etc.) go several days > (sometimes even weeks or months) without updating their databases. For > them--after migrating the files to the new server, a final check of > the binlog coordinates on the old box can verify that nothing has > changed. > > If it HAS changed, migration can copy the binlog file(s) to the new > box and use the mysqlbinlog command (specifying the starting > coordinates) to bring the new database in sync with the old one. > > (This scenario is probably oversimplified, ignoring things like DNS > caching, TTL, etc., etc. Hopefully it doesn't include too many lies.) > > > How I Plan to Do It. > ------------------- > > I've noticed that: > > 1. The class MYSQL_BIN_LOG seems to be involved in most binlogging > 2. MYSQL_BIN_LOG inherits from TC_LOG and MYSQL_LOG. It is created in > mysqld.cc at startup. > > My current partial implementation has: > > 1. Class DB_BIN_LOG inheriting from MYSQL_BIN_LOG, adding members like > db_name > db_binlog_name > db_index_name > and methods to access them, etc. > > 2. Singleton class USER_BIN_LOG_MGR. (Should probably be DB_BIN_LOG_MGR), > which maintains a db_binlog_hash of information about each DB being > binlogged. A DB_BIN_LOG_ENTRY (in the hash) contains: > dbname > locked (bool)--has it been locked by FLUSH ... TABLES WITH READ LOCK? > binlog file > binlog_pos > is_active > a pointer to a DB_BIN_LOG instance > > USER_BINLOG_MGR has a rw lock to protect access to its hash of > currently binlogging db's. (Most accesses will probably be > read-only?) > > Is it plausible to identify all places where a binlog addition might > occur? In those places, if the singleton USER_BIN_LOG_MRG exists, ask > it if the current DB is being binlogged. If so, send the request to > the DB_BIN_LOG instance. > > > Questions I Have. > ---------------- > > 1. I notice that binlog is in the list of engines and plugins > (displayed by SHOW ...). The bottom of log.cc has > "mysql_declare_plugin(binlog)". Do I need one for db_plugin? > (probably not?) > 2. I've diagrammed the binary log event taxonomy--the 24 classes that > inherit from Log_event (in log_event.cc/h). That was my main > motivation for wanting to inherit from MYSQL_BIN_LOG. > 3. On the other hand, I wonder if inheriting from MYSQL_BIN_LOG might > be overkill for "master only" binlogging--without the > slave/replication part of it. > 4. What version should I be working on? (We're currently running > 5.1.47.) What should I check out of bzr/launchpad? (Is that still > what we're doing with the changes in ownership?) > 5. I see that THD is involved in binlogging. (Several THD methods are > defined in log.cc.) Are there any caveats here? > 6. What files should I use? A new h/cc pair? ... or just add to the > hierarchy in log.h/cc? > > In an ideal world, it would be nice to inherit polymorphically from > MYSQL_BIN_LOG, overriding methods that set the names of the binlog > file and index file (etc.) then just call the methods and have it do > the "right thing." On the other hand, I get the impression that I > might be oversimplifying. > > I welcome your advice!! > > What I've Implemented So Far (about 13 Prototypes). > -------------------------------------------------- > > (PRELIMINARY NOTE:) > > Our initial plan was to call it "Per-User Binlogging". The user here > is a cPanel [control panel] user. cPanel's database naming convention > is to have all of a user's database names start with the username, > followed by an underscore, followed by a distinctive database > name. Thus all the commands above start with > > <verb> USER ... > > Originally most of them could end either "DATABASE <db>" or "USER > <user>" (with user meaning cPanel user). Thus, in the original plan > the first command above was: > > START USER BINLOG FOR (USER <user> | DATABASE <db>) > > We have realized that per-database binlogging is all we really need. > We imagine that others in our situation might want something similar. > Since not everyone uses cPanel, we should forget per[cPanel]-user > binlogging, focusing on per-database binlogging. > > I have several versions of lex and bison/yacc files (sql_yacc.yy, > lex.h) to implement the above (and earlier variants). The recent > versions avoid increasing the %expect'ed 169 shift/reduce conflicts > specified in sql_yacc.yy. > > (END OF PRELIMINARY NOTE) > > I currently have 13 different Subversion branches with different > (partial) implementations. > > 1. All have some lex/bison grammer defined. (I'm not completely > satisfied with any of them.) > > 2. Per-db binlogging is enabled by two my.cnf (or command-line) > options: > > user_binlog_dir=/full/directory/path > user_binlog_persist_file=user_binlog.info > > Our servers have fast solid-state drives that we will use for per-db > binlogging. We want to specify a separate directory to store all the > per-db binlogs > > The persist_file stores information about the current state of > db-binlogging--to survive a restart. > > Just about every instance of "user_binlog" in this note should > probably be changed to "db_binlog" ... (???) > > 3. I have added cases in sql_parse.cc for > > SQLCOM_START_USER_BINLOG_DB: > SQLCOM_FLUSH_AND_LOCK_DB: > SQLCOM_SHOW_USER_BINLOG_STATUS: > SQLCOM_UNLOCK_DB: > SQLCOM_STOP_USER_BINLOG_DB: > > (as well as some "USER" variants like SQLCOM_START_USER_BINLOG_USER:, > which should probably abandoned.) > > They should probably be changed to something like: > > SQLCOM_START_DB_BINLOG: > SQLCOM_FLUSH_AND_LOCK_DB: > SQLCOM_SHOW_DB_BINLOG_STATUS: > SQLCOM_UNLOCK_DB: > SQLCOM_STOP_DB_BINLOG: > > ----------------- > > Now that you've read it, please feel free to throw darts, etc. At this > point I'm willing to discard everytning and start over. (In the worst > case--if everyone on this list thinks per-db binlogging is a terrible > idea--I guess I'll be forced to create our own patch [sigh].) > > On the other hand, I would love to help contribute to this effort. (If > per-db binlogging is already underway, let me know, and I'll likely help.) > Thanks in advance for feedback/suggestions/help. Feel free to contact > me privately if you want to. > > Weldon Whipple > weldon(a)whipple.org > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=mdcallag@gmail.com > > -- Mark Callaghan mdcallag(a)gmail.com

3 2

[Maria-discuss] italian translation
by Federico Razzoli 12 Sep '10

12 Sep '10

Hello. I appreciate so much your new Knowledge Base. The pages that are already on line are very clear and complete, as the MySQL docs. But, unlike the MySQL docs, the Knowledge Base has free licenses. For these reasons, I would like to translate into italian - which is my natural language. Am I allowed to do that? As far as I understand, both the licenses used (CC BY-SA and GNU FDL) allow translations, as long as they have the same licenses used by the original work. But I want to be sure before starting. I can't translate all the articles, so I'll only translate some of the most interesting documents. But, as the documentation grows, maybe I may try to set up a group of volounteers. We'll see. Regards, Federico

2 1